
1. Re: Aggregating over an aggregation
Steve Mayer Feb 25, 2016 3:57 PM (in response to Jay Sridharan)In the attached workbook (Tableau 9.2), I created a nested LOD to solve this problem. LOD expressions are complex, but because of your subject title "Aggregating over an aggregation", I think you'll grok the solution. The formula looks like this:
{ FIXED [Team Number] : MAX({ FIXED [Team Number], [Match Number] : COUNTD([Id (Matchshoots)]) }) }
Breaking it down:
{ FIXED [Team Number], [Match Number] : COUNTD([Id (Matchshoots)]) }
Calculates the COUNT of distinct Matchshoots at the level of detail of Team Number and Match Number.
Then,
{ FIXED [Team Number] : MAX( ...) }
Calculates the MAX of the distinct COUNTS from the first LOD at the level of detail of Team Number.
Then you want to take the MIN (MAX or AVG are fine too) of that (the value of the nestedLOD calculation will be same for every Team Number, so you just need to grab one of 'em). The "Step 1" tab in the attached workbook illustrates this.
You end up with:
Steve

max.twbx 366.0 KB


2. Re: Aggregating over an aggregation
Jay Sridharan Feb 25, 2016 3:43 PM (in response to Steve Mayer)This looks great Steve, thank you. But I don't understand why we are taking the MIN. Aren't we trying to find the MAX?

3. Re: Aggregating over an aggregation
Steve Mayer Feb 25, 2016 3:58 PM (in response to Jay Sridharan)So the LOD formula itself is already finding the MAX:
{ FIXED [Team Number] : MAX({ FIXED [Team Number], [Match Number] : COUNTD([Id (Matchshoots)]) }) }
If you look at the LOD at the Team Number & Match Number level of detail, you'll see that the LOD calculation repeats (circled in red).
When you roll this up to the Team Number level, you need to aggregate (again). Turns out you can use MIN, MAX or AVG for this last aggregation (e.g. for Team Number 81, all of the values are 3, so you can take the MIN of 3 and 3, the MAX of 3 and 3, or the average of 3 and 3, and always get the same result. If you changed the agg to SUM, you would see 6 for Team Number 81, 16 for Team Number 93, etc.
Hope this helps. Like I said, LOD expressions (especially nested ones) are a journey to learn.

4. Re: Aggregating over an aggregation
Jay Sridharan Feb 25, 2016 4:00 PM (in response to Steve Mayer)Ah, that makes more sense now. Thank you very much!