1 Reply Latest reply on Oct 10, 2012 9:56 AM by Jonathan Drummey

    table calculation - comparing against a hierarchy

    Rama Kunaparaju

      Attached is a package and the excel file as an example. Sheet 'PIN Info' has Pins and Components. A pin can have multiple components. Sheet 'Hierarchy' has the component and the hierarchy that the component supercedes. Component A is supercedes components B,C,D according to the Hierarchy sheet. So if a pin has components A,B,C under it, the net components would be still 1 which is A, because A supercedes the rest.

       

      I want this calculation 'net components' to be done based on the hierarchy for each PIN. The result set should look like below.

       

      PIN     Net Components

       

      100             1

      200             1

      300              1

      400              1

      500              2   

       

      Any suggestions how we can do this in Tableau?

        • 1. Re: table calculation - comparing against a hierarchy
          Jonathan Drummey

          Hi,

           

          See the attached for a solution. PREVIOUS_VALUE is used to create the "Used Hierarchy List".  Then in the "Total Top Level Members" calc, the CONTAINS() function is used to identify if the  particular Component is found within the hierarchy - if not, then it must be a top-level (superceding) member and so it is counted in the WINDOW_SUM. The Total Top Level Members calc assumes that the components within the PIN are sorted by position in hierarchy (descending), if they aren't then a differnet

           

          The Compute Using for all calcs is set to Component. Note that I wrapped the Hierachy$_Hierarchy field in ATTR() so it would be ignored for partiitioning and addresssing in the table calcs.

           

          Cheers,

           

          Jonathan