
1. Re: Retention Cohort Analysis
Jonathan Drummey Nov 15, 2017 12:43 PM (in response to Jatin Kakani)Hi, I have a question  you have hidden the Null value of months_since_activation_exp which does not remove it from the table calculation total of 376. Do you really want 11/376 or 11/336 (where 336 is the number of merchants with the Null value filtered out)?
Jonathan

2. Re: Retention Cohort Analysis
Jatin Kakani Nov 15, 2017 12:43 PM (in response to Jonathan Drummey)Hi Jonathan,
Thanks for replying.
I want 11/376. I am looking to calculate the percentage of expired merchants over the number of active merchants at that point of time.
Jatin

3. Re: Retention Cohort Analysis
Patrick A Van Der Hyde Nov 15, 2017 12:45 PM (in response to Jatin Kakani)Jatin Kakani and Jonathan Drummey
I went ahead and branched this question off as a new discussion since it was added to the a question from several years ago. i also gave it a meaningful title for the question. This way other members of the community are more likely to see and contribute.
Patrick

4. Re: Retention Cohort Analysis
Jatin Kakani Nov 15, 2017 1:58 PM (in response to Patrick A Van Der Hyde)Thanks Patrick !

5. Re: Retention Cohort Analysis
Jonathan Drummey Nov 18, 2017 11:59 AM (in response to Jatin Kakani)Here's a solution, it's a nice example of when we need to use a custom sort on a table calculation:
Here's the workout view:
I'll walk through the calcs I added & changed:
date_filter jtd removes the IF/THEN from the date_filter calc you'd built, that is faster for Tableau to process and simpler to right.
Instead of using Hide I added a Null months_since filter calculation with the formula NOT ISNULL(LOOKUP(MIN([months_since_activation_exp]),0)). This is a table calculation so it will act like the Hide in terms of the display and since it's a filter it will ultimately end up on the Filters Shelf so we can see it there. It's got a second use as a flag.
RS Merchants has the formula RUNNING_SUM([No. of merchants]) and a customized Compute Using that uses a filter on months_since_activation... in Descending order. This acts as the descending total of merchants. NOTE  this running sum depends on the fact that each Merchant ID only has a single months_since... value for each Year & Month of Activation date. If for some reason a Merchant ID could have multiple months_since.. .values for a single Year & Month of Activation date then a different technique would need to be used since the view would then need something closer to a running count distinct and that's not a native function.
Null merchants has the formula PREVIOUS_VALUE(ZN(WINDOW_MAX(IF NOT [Null months_since filter] THEN [No. of merchants] END))). The PREVIOUS_VALUE() is there is an optimization, what the rest of the calc does is pad out the No. of merchants value of the Null months_since... to the rest of the months_since... values so we can add that to the running sum. This has a compute using on months_since....
Total merchants jtd has the formula [RS merchants] + [Null merchants].
Finally, churn percent jtd has the formula [No. of merchants]/[Total merchants jtd].
I then duplicated the workout view and moved pills around to build the crosstab, heatmap, and lines views.
Jonathan

Churn Analysis_test jtd.twbx 14.0 MB
