Different Aggregation calculations of a measure using two dimensions in Tableau

Question

It is a Tableau 8.3 Desktop Edition question.

I am trying to aggregate data using two different dimensions. So, I want to aggregate twice: first I want to sum over all the rows and then multiply the results in a cummulative manner (so I can build a graph). How do I do that? Ok, too vague, here follow some more details:

I have a set of historical data. The columns are the date, the rows are the categories.

Easy part: I would like to sum all the rows.

Hard part: Given this those summations I want to build a graph that for each date it shows the product of all the summations from the earlier date till this date.

In another words:

Take the sum of all rows, call it x_i, where i is the date.

For each date i find y_i such that y_i = x_0 * x_1 * ... * x_i (if there is missing data, consider it to be one)

Then show a line graph for the y values versus the date.

I have searched for a solution for this and tried to figure it out by myself, but failed.

Thank you very much for your time and help :)

Have you tried the Running Total under Quick Table Calculation? If so, why doesn't that work? — coge.soft
Hi @coge.soft thank you for the reply. The problem arises when I try to use both calculations. I want to sum the rows and then do a running total that is a multiplication of the current value and the immediately before. So I am having trouble trying to use those two calculations altogether. — Breno
@coge.soft, actually I am trying this approach now, but the results just does not match. :/ What I am doing is creating a field that is Cumulative = EXP(SUM([Measure1])) and a Cumulative_run = LN(RUNNING_SUM([Cummulative])) I used LN and EXP because EXP(LN(a)+LN(b)) = a*b, not an elegant approach but I think it might help. — Breno
it sounds like you are trying to create data. For instance, are you effectively trying to do a cross join in tableau? — coge.soft
Indeed, I am trying to process data in order to generate new and more meaningful information. Now I am using Tableau 9 and I am willing to use Level of Detail expressions to solve this same problem. I tried the following (but it is not working:/) EXP(RUNNING_SUM({include [Date]: LN(SUM([Value])})). Do you have any suggestions? — Breno

Inox Inox · Accepted Answer · 2015-04-08T10:23:21

You need n calculated fields (number of columns you have), and manually do the calculation you need:

y_i = sum(field0)*sum(field1)

Basically because you cannot iterate on columns. For tableau, each column represent a different dimension or measure. So it won't consider that there is a logic order among them, meaning, it won't assume that column A comes before column B. It will assume A and B are different things.

Tableau works better with tables organized as databases. So if you have year columns, you should reorganize your data, eliminate all those columns and create a single field called 'Date', which will identify the value of your measure for that date. Yes, you will have less columns but far more rows. But Tableau works better this way (for very good reasons).

Tableau 9.0 allows you to do that directly. I only watched a demo (it was launched yesterday), but I understand that now there is an option to selected those columns (in the Data Connection tab) and convert them to a database format.

With that done, you can use a PREVIOUS_VALUE function to help you. I'm not with Tableau right now. As soon as I get to it I'll update this with the final answer . Unless you take the lead and discover yourself before that ;)

Different Aggregation calculations of a measure using two dimensions in Tableau

1 Answers