4 Replies Latest reply on Feb 25, 2016 4:00 PM by Jay Sridharan

    Aggregating over an aggregation

    Jay Sridharan

      Hey Guys,

       

      I'm sure this question has already been asked, but I guess I'm not quite sure what I'm searching for or where to apply it in my worksheet.

      I'm very new to Tableau, sorry!

       

      Basically, I've got a chart that looks like this:

       

      It is getting the number of records in matchShoots / match / team. I need to get the max number of records / team.

       

      In other words, here is an excerpt of the data being represented now:

       

       

      This is what I would like to get:

       

       

      The workbook is attached.

       

      Thank you for all the help!

        • 1. Re: Aggregating over an aggregation
          Steve Mayer

          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 nested-LOD 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

          • 2. Re: Aggregating over an aggregation
            Jay Sridharan

            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

              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

                Ah, that makes more sense now. Thank you very much!