I'm trying to do a business retention analysis, in this case on auto insurance policies. I've had help and made some progress, but I never seem to be able to get the final desired report.
My data set is simple: Policy ID, Date the Policy Started, and Cancel Date (if not active)/Expiration Date (if still active).
It's easy enough to look at policies written 3 years ago and see what % are still active today, but I want to see what % were active after 1 month, after 2 months, ..., after 3 years.
Also a policy written, say 3 months ago, I don't want it included when determining what % are active in 3 years. Of course it's not been active for 3 years because that's impossible.
So attached is what I have so far. I have 1 viz showing % of policies still active after every 30 days. The other viz shows how many days it could have possibly be active. For example, if a policy was written 3 years ago, but cancelled after 2 months, it COULD have been active for 3 years.
Basically the number I'm looking for is the bin value in viz 1 divided by the bin value in viz 2. For example, For 150 days, 49/86 for 57%.
I'm not married to using bins for this. I'm not even married to the data set I'm using if I re-pull it a different way. In my original data set, what I don't have easily available is a list of active policies each month. So that solution is not an option.
Side note: I can't get 0-30 days to populate with 100 on the Days Possible viz because I don't have any policies written <30 days ago so it's not considering the starting point a missing value.
Thanks in advance for your help.
Retention Sample.twbx 35.6 KB