7 Replies Latest reply on Jul 6, 2016 11:32 PM by Nikhil Chhazed

# How to do Double Aggregation using Tableau

Hi,

I am having a hard time doing a double aggregation in Tableau.I have a certain user data and using some set of rules(using Calculated Field) i determine which state currently they are in.

Now i used LOOKUP function to compare their current state from previous month again using another Calculated Field lets say i call them "Tranformations".

Now i want to calculate the Count Of Users for each type of transformations for a month. The results should show up as

Transformations March April May June

Same              3      2   1    0

Ignore            0      2   2    4

Gain              0      0   1    0

Decline           1      0   0    0

A Possible way to do this that i can think of was to publish The "Transformations" Workbook as a Data Source so that it doesn't treat the Values Of Transformations as Aggregated Field. But i dont think this way is supported in Tableau.

I would appreciate any help on this!! I have attached the Tableau Workbook for reference.

• ###### 1. Re: How to do Double Aggregation using Tableau

I can answer your title question, but it's the fourth of July weekend and I'm not up for picking through your workbook. (Thanks for posting one btw!) Tableau introduced aggregating aggregates when they introduce LOD Expressions. You can (actually have to) aggregate the right side of the colon. But once you put this inside the LOD brackets {  } then Tableau treats this as a non-aggregated value. So when you use this LOD expression in another calculation you will be required to either aggregate everything (including the LOD Expression), or not have any aggregations at all.

Sounds like if you can turn your first level of calcs into LODs, you can then aggregation these calcs again.

Just a thought. Happy fourth!

--Shawn

Too bad the BREXIT vote didn't happen on the 4th of July, then we could both celebrate our independence on the same day! (Just kidding.) History is a tricky thing!

2 of 2 people found this helpful
• ###### 2. Re: How to do Double Aggregation using Tableau

Hi Nikhil,

We've got no holidays on 4th July here in Russia ;-)

So I'll take a chance to introduce you to a common approach

to aggregating results of table calculations (some of them)

using RANK table calculations (proposed by Joe Mako i think).

It's a bit complicated, anyway ...

The technique is well explained

in a blog post by Alexander Mou here:

Vizible Difference: Histogram via Rank Functions

Hope it could help.

Yours,

Yuri

3 of 3 people found this helpful
• ###### 3. Re: How to do Double Aggregation using Tableau

What is the 'Independence' date (or probably dates) for Russia? What's the current 'independence' date, and what have they been from the day of the last Tzar moving forward? Should be an interesting timeline viz!

Cheers,

--Shawn

• ###### 4. Re: How to do Double Aggregation using Tableau

Awesome Yuriy,

You have saved me a lot of effort this is what i was looking for thanks a lot!!

• ###### 5. Re: How to do Double Aggregation using Tableau

Thanks Shawn Wallwork, the info was helpful but looks like i cannot use Table calculation or ATTR within the LOD expression. Since i need to get the previous value to compare with the current value(similar to what is Lead/lag in SQL) i don't know how would LOD help in my case, could you please explain it the worksheet, of course after the Independence day!! .

Anyways Happy Independence Day!!

• ###### 6. Re: How to do Double Aggregation using Tableau

The official "unofficial independence day" is called Russia Day:

Russia Day - Wikipedia, the free encyclopedia

Before that there were two revolutions in 1917

https://en.wikipedia.org/wiki/Russian_Revolution

And the Russian Constitution has been updated

four times since -- and I mean major updates.

So one could call us "independent" many times a century :-)

Yours,

Yuri

• ###### 7. Re: How to do Double Aggregation using Tableau

Yuriy Fal I was able to get the data in the following format:

Transformations March April May June

Same              3      2   1    0

Ignore            0      2   2    4

Gain              0      0   1    0

Decline           1      0   0    0

Is there a way i can do another aggregation on top of the values for each month, something like

"Net = Gain - Decline"

Transformations March April May June

Same              3      2   1    0

Ignore            0      2   2    4

Gain              0      0   1    0

Decline           1      0   0    0

Net               -1     0   1    0