Thanks for your post!
Unfortunatly in your solution the accumulative result is not correct (it
does not match with the table that I included in the email).
Thanks for your post! It really helped me to solve this specific problem.
Anyway do you know if there is an additional way to solve it that does not need to create Sets manually?
The fundamental issue is that we want to partition the data on a measure, the measure being the Accum Frequency that is the total number of months the user is in the data set so far. Within the partition of the Month & Accum Frequency, we then want to sum up the # of users and their sales.
Starting from the raw data, we want to count each user id/month combination up to N times, where N is the number of months that user is in the data set. So we need to simultaneously partition the data on a measure and count multiple rows of the raw data together, which means simultaneously working at multiple levels of detail in the view.
Table calcs are a common way to do that, and the original Accum Frequency calc would be great to use if we could partition by table calculations, but we can't. Here's an idea for that: http://community.tableau.com/ideas/1194. There are some hacks that we've come up with over the years to use additional table calculations to effectively partition by table calculations. I tried that route and it got super-complicated, crashed Tableau multiple times and probably would have bad performance anyways if there were more than a few thousand User Ids.
We can partition on discrete aggregate measures via turning off Ignore in Table Calculations for that measure's pill in the view. So to change the Accum Frequency computation from requiring a table calculation to being a regular aggregate I "pushed down" some of the computation into the data source. I used Custom SQL to generate a cross product of each User Id and all their prior months, for each month. This gives us enough rows of data to then generate accurate counts. The Accum Freq calc can be SUM(Number of Records) for this data set, after turning off Ignore in Table Calculations I could use it to partition other calculations to determine the # of customers and Sales.
You can see the "workout jtd" worksheet for the calcs getting worked out, and the "view jtd" worksheet for the final view. This solution is dynamic, and doesn't even require a data extract.
Thanks for your explanation! I thought the solution was more trivial...I was really far away from solving it.
It is clear the "limitation" of table calcs for this case and the turnaround using custom SQL.