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)?
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.
Thanks Patrick !
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.
Churn Analysis_test jtd.twbx 14.0 MB