
1. Re: Accumulative Calculation...probably very easy but I don't get it
Manas Ranjan Kar Jan 10, 2014 12:22 PM (in response to Juan Pablo Bruzzo)Hi,
Does this help? I have computed Accum Freq using User ID, and changed Customer count to simple count not distinct count. Rest remains the same.
Cheers !

sample_tableau.twbx 23.0 KB


2. Re: Accumulative Calculation...probably very easy but I don't get it
Juan Pablo Bruzzo Jan 10, 2014 2:52 PM (in response to Manas Ranjan Kar)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!

3. Re: Accumulative Calculation...probably very easy but I don't get it
Ville Tyrväinen Jan 11, 2014 10:54 PM (in response to Juan Pablo Bruzzo)
sample_tableau_vt.twbx 22.6 KB


4. Re: Accumulative Calculation...probably very easy but I don't get it
Juan Pablo Bruzzo Jan 13, 2014 10:14 AM (in response to Ville Tyrväinen)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?
Best,
J

6. Re: Re: Accumulative Calculation...probably very easy but I don't get it
Jonathan Drummey Jan 14, 2014 9:03 AM (in response to Ville Tyrväinen)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 supercomplicated, 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.
Jonathan

7. Re: Re: Accumulative Calculation...probably very easy but I don't get it
Juan Pablo Bruzzo Jan 14, 2014 11:42 AM (in response to Jonathan Drummey)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.
Best,
JP