I am currently trying to create a Pivot-Table, which should display data from 3 different hierarchies (H).
H1 -> 1 or more H2
H2 -> 1 or more H3
- H1 =(avg(x))
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