Pivot table - using formulas depending on the hierarchie

Anonymous — Thu, 06 Oct 2016 07:09:49 GMT

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)

...

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

Re: Pivot table - using formulas depending on the hierarchie

Anonymous — Mon, 10 Oct 2016 09:49:33 GMT

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

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

topic Re: Pivot table - using formulas depending on the hierarchie in App Development

Pivot table - using formulas depending on the hierarchie

Re: Pivot table - using formulas depending on the hierarchie