An insurance can in any given time period be either
b. Not active
If one looks at a given time period, active insurances can be categorized as either unchanged, new, short-time or drop-out.
1. Unchanged: The insurance was active at the start and at the end of the period.
2. New: The insurance was not active at the start of the period but it was active at the end of the period.
3. Short time: The insurance was neither active at the start nor at the end of the period, but it was active some time during the period.
4. Drop-out: The insurance was active at the start of the period but not at the end of the period.
The aim is to, given a data set only containing effective and expiration dates for insurances, plot the percentages of the four categories mentioned above for each time period. This is easily done for one time period. However, I have not been able to calculate the number of policies that fall into each of the categories over time as I do not have a date variable that fits as neither effective date nor expiration date would be correct to use as the time axis.
One might for instance be interested in looking at how the monthly drop-out rate has changed over the last three years, or a diagram showing the percentage of the insurance policies that were new in each and every week over the last five years.
In order to do this we also want to be able to choose:
- Date level for the period we want to base the calculations on (days, weeks, months, years)
- How many such periods back in time we want the graph for, e.g. 13 weeks back.
Attached are both a Tableau workbook with mock data and an excel file where I have tried to show what I would like to do (using manual calculations).