
1. Re: Plotting cumulative cancellations by tenure (workbook attached)
Shinichiro Murakami Nov 14, 2016 8:03 PM (in response to Maximus Decimus Meridius)At first, pivot data thru edit data source.
Create Parameters with dates.
Create several calculated fields.
[Enroll ID]
if [Pivot field names]="Enroll Date" then 1 else 0 end
[Canceled ID]
if [Pivot field names]="Cancel Date" then 1 else 0 end
[Canceled ID negative]
[Canceled ID]*1
[Cum ID]
[Enroll ID]+[Canced ID]
[Tenure at Start]
{fixed : sum(if [Pivot field values]<[Start Date] then ([Cum ID]) end)}
[Tenure at date]
attr([Tenue at Start])+running_sum(sum([Cum ID]))
[Date Filter]
if [Pivot field values]>=[Start Date] and
[Pivot field values]<=[End Date]
then "Show" else "Hide" end
Put table calculation for [Canceled ID] and [Enrolled ID]
Thanks,.
Shin

2. Re: Plotting cumulative cancellations by tenure (workbook attached)
lei.chen.0 Nov 14, 2016 9:23 PM (in response to Maximus Decimus Meridius)Hello Maximus,
This is a little bit like cohort analysis.
Besides Shinichiro Murakami 's solution, here comes another approch, LOD+Table Calculation.
I attached a sample workbook and a reference link.
Top 15 LOD Expressions  Tableau Software
Regards

3. Re: Plotting cumulative cancellations by tenure (workbook attached)
Shinichiro Murakami Nov 14, 2016 10:44 PM (in response to lei.chen.0)Thank you Lei Chen.
I mistook the goal. it should be the days from enrollment, not date..
My bad.
Regards,
Shin

4. Re: Plotting cumulative cancellations by tenure (workbook attached)
Maximus Decimus Meridius Nov 15, 2016 9:23 AM (in response to lei.chen.0)Thanks, having a look at this first before I start digging through Shinichiro's solution. This looks like it's listing the number of cancels at each tenure (x people cancelled on day y), which I guess can also be achieved by showing the running total of records by tenure (where tenure = cancel date  enroll date)? I do get the same graph as you with this approach
What I'm trying to show though is the value as % of total of all records in that cohort, i.e. at day 90, 13% of everyone with who joined 90 days ago or more have cancelled etc. I tried just dividing by total(sum([number of records]), but that only summed up cancels (so at day 194 it gives 100%). Do you know how this could be achieved?
This is what I used in R
sapply(seq(1:500), function(x) sum(data[data$tenure < x,]$members, na.rm = T)/sum(data[data$life >= x,]$members))
That is, for each value of x from 1 to 500, I compute the number of people who cancelled within x days, divided by the total number of members who joined at least x days ago (here tenure means days of membership before cancelling, while life means how many days ago they joined). This gives me a vector of percentages that I can then plot to see where in the membership we have spikes in cancellations, which yields the graph mentioned in the first post. Thing is in R there's some work involved breaking this cancellation out by other variables, whereas in tableau, if I could figure out a way to compute this I'd be able to just drag and drop to get the cancellation curves by age, state and all kinds of things very easily.
Thanks again for the help!

6. Re: Plotting cumulative cancellations by tenure (workbook attached)
Maximus Decimus Meridius Nov 15, 2016 3:09 PM (in response to Chris Chalmers)Thanks, this is super helpful but man this is turning out to be way harder than I thought...as a sanity check, you can see that 202,743 have a life of 91 days or more, and out of those 27,226 cancelled in the first (had tenure less than or equal to) 90 days. So the 90 day cancellation rate should be 13.43%
So in your book 'Running Count of Tenure < x' is counting everyone with tenure less than x, but it should only count those who also have life greater or equal to x. 'Running Count of Life >= x' is showing 296K having life of 198 or greater, but the real number of records with 198 life (the max life) is 1.9K
Basically the formula I'm trying to compute is this:
(# of records with life >= x AND Tenure < x)/(# of records with life >= x)
I managed to get the correct count of life >=x by using
total(sum([Number of Records]))RUNNING_SUM(SUM([Number of Records]))
and then plotting against life + 1 (which gives the numerator). But I have no idea how to get the total of people with tenure < x and life >= x for the denominator...
Thanks again for the help

7. Re: Plotting cumulative cancellations by tenure (workbook attached)
Chris Chalmers Nov 15, 2016 11:46 PM (in response to Maximus Decimus Meridius)Your visualization is challenging because it requires combining two table calcs operating on differentlyordered data (tenure and life) onto the same axis. LOD and table calcs don't mingle well. I can get your desired numerator and denominator on separate sheets, but I've exhausted my knowledge of the product trying to merge them onto one axis. Hopefully someone knows a trick and can shed some light.
Best of luck,
Chris Chalmers

8. Re: Plotting cumulative cancellations by tenure (workbook attached)
Shinichiro Murakami Nov 16, 2016 7:56 AM (in response to Maximus Decimus Meridius)OK, my turn again.
Still not sure I understand the request very correctly, but here is some trial.
[Days]
max(0,[Cancel Date][Enroll Date])
[Days 2]
if isnull([Cancel Date])
then today()[Enroll Date]
else [Days] end
[Cancelled or Not]
if isnull([Cancel Date]) then "Existing" else "Cancelled" end
[Percentage]
running_sum(count(if [Cancelled or Not]="Cancelled" then [Member ID] end))
/
(attr({fixed:count([Member ID])})running_sum(count(if [Cancelled or Not]="Cancelled" then [Member ID] end)))
Thanks,
Shin

9. Re: Plotting cumulative cancellations by tenure (workbook attached)
Maximus Decimus Meridius Nov 18, 2016 3:46 PM (in response to Shinichiro Murakami)Thanks for keeping trying, but after hours trying to make this work I'm just not seeing a way. The problematic part is that I need to count the number of people who have cancelled under x days, but only from the members who have joined at least x days ago. The issue is that at 91 days it says 36.5K cancelled, but these are people who cancelled in the first 91 days out of everyone, so it neglects the cohort approach. The real number I've been trying to get to is 27K.
People who have only joined a few days ago will have almost 0% 90 day cancellation rate, even though a good chunk of them will actually end up cancelling in the first 90 days, so it skews the data if you don't filter on minimum life...
I really can't think of a way around this