6 Replies Latest reply on Jun 19, 2018 7:50 AM by Darren Braynard

    Aggregation of aggregation question

    Darren Braynard

      Hi, very new user here...


      I have a problem very similar to the one here: Re: Calculation to return the maximum value for each user?


      Here is the concept. I start with this data (for simplicity there is only one model in this example). A model has many views and a view has many elements. Suppose a session is the result of analyzing the view and recording elements that had errors.






      I can create this next view using a calculated field and filter:




      I did this with the help of a calculated field similar to the one in the above linked thread, and I applied the filter with a the SessionId as the specific dimension:

      IF MAX([SessionId])=WINDOW_MAX(MAX([SessionId])) 
      THEN true 
      ELSE false  


      Now, I need help with creating the next view which is a summary of the previous sheet.




      I would like to avoid doing this entirely in MS SQL in views with sub-queries and instead use the power of tableau. I tried LOD calculations but could not figure out how to apply them in this case.


      Thank you in advance.

        • 1. Re: Aggregation of aggregation question
          Ken Flerlage

          I'd suggest a series of calculated fields as follows:


          Element Count

          // Get Distinct Count of all Elements

          COUNTD([Element Id])


          Total Elements Per View

          // Get the max number of elements in each view.

          {FIXED [View]: MAX([Total Elements In View])}


          Total Element Count

          // Sum up the total elements per view.

          SUM([Total Elements Per View])


          Error Rate

          // Calculate the error rate.

          [Element Count]/[Total Element Count]



          See the attached workbook.


          If this addresses your need, please be sure to mark this answer as correct. Thanks!!

          1 of 1 people found this helpful
          • 2. Re: Aggregation of aggregation question
            Darren Braynard

            I appreciate the effort Ken, but I think that I'm looking for a slightly different answer. The "Element Count" (i.e. total number of elements with errors across the Model) should be 5, not 6. And therefore, the ErrorRate should be 5.3%.

            • 3. Re: Aggregation of aggregation question
              Hima vardhan Reddy Pavuluri

              Hi Check the below calculations :


              Last Session Id : { FIXED [Model],[View] : MAX([Session Id])}


              Error Element Count :

                                                      { FIXED [Model],[View] : COUNTD(

                                                      (IF [Session Id]=[LS] THEN [Element Id] END))}


              Total Elements : { FIXED [Model],[View] : AVG(IF [Session Id]=[LS] THEN [Total Elements In View] END) }


              Error Rate :  (SUM([ErrorElementCount])/SUM([TotalElements]))*100


              and the result are below :


              attached the workbook for reference.

              1 of 1 people found this helpful
              • 4. Re: Aggregation of aggregation question
                Mahfooj Khan

                Hi Darren,


                Please find my approach,


                Create few calculated fields using below mentioned formula


                [Max Session]: {FIXED [Model],[View]:MAX([Session Id])}  // It will result last session id per view

                [Elements]: COUNTD(IF [Session Id]=[Max Session] THEN [Element Id] END) THEN [Element Id] END) // It'll result distinct count of total element per view as per the last session

                [TotalElementsView]: SUM({FIXED [Model],[View]:MAX(IF [Session Id]=[Max Session] THEN [Total Elements In View] END)}) //Formula is quite similar to above one only change in agg() and measure. It returns TotalElementView of latest session per view.


                Error Rate %: [Elements]/[TotalElementsView]


                Now drag the fields in canvas and see the output

                Final View: Remove unwanted fields from the canvas


                Let us know if this help. Workbook v10.5 attached for your reference.



                1 of 1 people found this helpful
                • 5. Re: Aggregation of aggregation question
                  Darren Braynard

                  This works, I appreciate the help!

                  • 6. Re: Aggregation of aggregation question
                    Darren Braynard

                    This also works, I appreciate the help!