1 Reply Latest reply on May 20, 2016 3:22 AM by Nagarajan R

    Tableau 9.2 - Table Calculation to do Sum on Max

    Nagarajan R

      Hi,

      Requirement:

      On Drill up/down hierarchy (say Category --> Subcategory --> Product), I need to show max (sales)  when viewed at Product level and when rolled up to Subcategory, it should do sum of all max values at product level.

      Basically I am trying to create a SUM (MAX(Sales)).

      Option Tried so far:

      I am able to achieve this with below LOD expression:

      {FIXED [Product] : MAX (Sales)}

      This LOD field gives me max at product level and when rolled up to (+)Subcategory it adds up all max values and shows the expected result.

       

      However the challenge is performance with LOD.

      I use a Live connection and data source design includes joining 6 tables with primary table having 1.1 billion records.

      Though I apply filters (on filter shelf), it takes a long time.

      Note: With filters when I query against DB directly only 2330 records are pulled. so tableau with live connection and filters applied is finally needing to work with 2330 records.

      However even with that dataset with LOD it is taking long time.

       

      So was wondering if there is a way to achieve the same SUM on MAX functionality with Table Calculations?

       

      Please help!!

       

      Thanks

      Nagarajan

        • 1. Re: Tableau 9.2 - Table Calculation to do Sum on Max
          Nagarajan R

          Managed to do this (though partially only) with Table Calculations. Below is the definition I used

           

          IF FIRST()==0 THEN

          WINDOW_SUM(MAX(Sales),0,IIF(FIRST()==0,LAST(),0))

          END

           

          Placed the lowest level (ie Product ) in the detail shelf and change compute using to Product.

           

          This worked when we roll up/down at Category and Subcategory levels but when down to Product it still gives the aggregated value.

          So only a partial solution

          1 of 1 people found this helpful