1 Reply Latest reply: Oct 10, 2016 5:49 AM by Dennis Odenbreit RSS

    Pivot table - using formulas depending on the hierarchie

    Dennis Odenbreit

      I am currently trying to create a Pivot-Table, which should display data from 3 different hierarchies (H).

       

      Logic:

      H1 -> 1 or more H2

      H2 -> 1 or more H3

       

      Pivot:

           - H1 =(avg(x))

                +H2 =(sum(x))

                +H2

                -H2

                     H3 =(x)

                     H3

                     ...

       

      Those Hierarchies are currently stored in 2 tables (H1 in table 1, H2 and H3 in table 2).

      Those tables are connected by a key.

       

      I want the top Hierarchie H1 to be displayed (as shown) as the first item in the pivot table, H2 as a subitem and H3 as a subitem of H2.

      That is not a problem.

      The problem starts when trying to add a formula, which applies to all 3 hierarchies, but is calculated differently in hierarchie H1.

      Since I can only use 1 formula for all 3 hierarchies combined, how can I manage to calculate values differently depending on the hierarchie?

      I found the IF() condition to be helpful, since the value of H2 happened to be null sometimes after the qlik-sense automatic join over the table key, allowing me to address H1 if H2 is null.

      Unfortunately that is only the case if H1 has more than 1 H2 items, otherwise it is not null.

      Is there a way to solve this problem?

      If so, please let me know