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.

       

           

      ModelViewSessionIdElementIdTotalElementsInView
      1Front10A50
      1Front10B50
      1Front10C50
      1Back11D40
      1Back11E40
      1Back11F40
      1Front12A55
      1Front12B55

       

       

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

            

      ModelViewLastSessionIdElementErrorCountTotalElementsInViewErrorRate
      1Font122553.6%
      1Back113407.5%

       

      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  
      END
      

       

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

          

      ModelElementCountTotalElementsInModelErrorRate
      15955.3%

       

      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.

                 

                Mahfooj

                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!