Tableau table with calculations on multiple dates

0
votes

I have a table of policies with effective & expiration dates. I want to know how many policies were in effect during each month.

Raw Table

id, effective_date, expiration_date
1, 2020-01-01, 2020-06-01
2, 2020-02-01, 2020-07-01
3, 2020-03-01, 2020-08-01
4, 2020-04-01, 2020-09-01
5, 2020-04-01, 2020-09-01

Ideal Output

Month, Count
Jan, 1
Feb, 2
March, 3
April, 5
May, 5
June, 5
July, 4
August, 3
Sept, 2

I was thinking I could do something like the following

Effective_Count

RUNNING_COUNT(COUNT([policies]))

Expired_Count

RUNNING_COUNT(COUNT(
  IF(policy.expiration_date < RowMonth)
  THEN 1
  ELSE 0
  END 
))

And then the policies_in_effect would just be

Effective_Count - Expired_Count

I'm not able to get the RowMonth value though.

1
tableau-api

1 Answers

1
votes

Pivoting will help. One more thing, even if policies are expiring on first day of month, you are considering these active on that day so you'll have to add one day in the expiration month.

Step-1: In the data tab, select both date fields and select pivot

Preview-1

Rename both fields as per your liking. Then add a calc field to add that extra day/month as DATEADD('month', 1, [Date]). Then add another calc field with IF THEN ELSE condition.

If [Event] = 'effective_date' then 1 else -1 END

Add calcF 1 on Rows --> right click select month --> change it to discrete -> again right click and check show missing values

Add calc F2 on text marks --> right click --> add table calculation --> edit to running total like this

!Preview2]2

This will give you your desired result

Result