Pivot table - using formulas depending on the hierarchie

[Anonymous]

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

[Anonymous · Accepted solution]

The solution I were looking for is the dimensionality() function.

With its help i can easily use a formula based on the hierarchy.