3 Replies Latest reply on Jul 23, 2012 2:55 PM by Jonathan Drummey

    Table calc using subtotal with hierarchies

    John Liska

      I have a table where I'm trying to create a calculated value that is persistent. However, this table has 4 levels of hierarchy which the user can choose to expand/collapse, and that seems to be throwing things off.I created a table calc and have made many mods but nothing quite works.

       

      Here is the table, and it is *almost* correct:

       

      Capture.PNG

       

       

      A few notes about the table:

      • User - Attribute (dimension) and User - Measure Year2 (measure) are both fed in via parameters.
      • The hierlevelA thru hierlevelD fields are part of a hierarchy which the user can expand/collapse as necessary. Here I have it shown fully expanded.
      • The last column -- Calculation5 -- is what I'm trying to build. It is currently a table calc using Table(Down) and the formula of:

                WINDOW_SUM(SUM([User - Measure Year2]))

       

      I know that's not correct but I'm using it for illustrative purposes. The key functionality that *does* work in this case is that no matter what level the user is on in the hierarchy (A, B, C, or D) the column persists the value of 57,892 for every detail record. (I'm going to need this column later for building some indexes.)

       

      What I need it to do is for any level in the hierarchy (A, B, C, or D) have the column persist the value of the SUM of User - Attribute. (Instead of the SUM of the entire table.) In this case: 33,495 for "SALTY" and 24,396 for "SAVORY". Using a table calc with Pane(Down) does not work because as the user expands the hierarchy it recalcs for the new panes.

       

      Any thoughts or insights are appreciated. Thanks.

        • 1. Re: Table calc using subtotal
          Jonathan Drummey

          Hi John,

           

          Unfortunately, table calculations are sensitive to the discrete non-aggregate pills in the view. As you've seen, whenever a user expands or collapses the hierarchy, that changes the partitioning of the table calculations and, given your data set, the results change. There might be a way to get the total you want using a combination of PREVIOUS_VALUE(), LOOKUP(), and other calcs, but that will get really messy really fast, and not be particularly fast.

           

          So, here's an alternative - duplicate your data source, then edit the relationships to delete the hierarchy levels as linking fields, so the only field that links is the User Attribute. Then you can drag calculations from the secondary data source into the view and have them aggregated at the proper level and be invariant to setting of the pills.

           

          I've set this up in the attached using Superstore Sales data, using Region in place of User Attribute and the Category/Sub-Category/Product Name hierarchy.

           

          Cheers,

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: Table calc using subtotal
            John Liska

            Thanks Jonathan. This is an interesting approach, I will try playing around with this. My initial concern would be the size of the Tableau data set. This approach will double the size, correct?

             

            And I believe the answer is "yes" but I'll ask anyway: will this approach work if -- as is the case for what I'm building -- the User Attribute field (the "Region" field in your example) is a parameter? As such it could be 1 of many dimensions that the user can pick. The way your calcs are set up they will work regardless, no?

             

            Thanks again for your help.

             

            JPL

            • 3. Re: Table calc using subtotal
              Jonathan Drummey

              Hi John,

               

              Yes, this approach will double the size if the data is files and not live. An alternative if you are using Tableau Data Extracts is that the secondary data source could connect to same extract used in the primary data source.

               

              To use a parameter to pick the dimension, you're going to need to have the calculated field that returns the appropriate Dimension in both the primary and secondary, and edit the relationships so that is a linking field between the two data sources. I hadn't done this before, so I set it up in the attached. so you can see it at work.

               

              Jonathan