topic Re: how to Maintain static total & sub total value in pivot table irrespective of the selection in QlikView

how to Maintain static total & sub total value in pivot table irrespective of the selection

shwethaa — Wed, 25 Nov 2020 16:16:04 GMT

I have data like this and if i load this to pivot table

Dim1, Dim2, Dim3, Fact1, Fact2

A, AA, AAA , 10, 20

A, AB, AC, 6, 12

A, AB, AD, 5, 12

B, BA, BB, 2, 4

if we select Dim1=A and Dim2=AB then total for Dim1,Fact1 should be 21. and Dim2, Fact1 total should be- 11

Required Output:

Dim1	Dim2	Dim3	Fact1	Fact2
A total	-	-	21	44
A	AB Total	-	11	24
A	AB	AC	6	12
A	AB	AD	5	12

Any idea how to achieve this static values for total and subtotal in pivot table? And it should display only selected dimensions. In this case it should show only A under Dim1, not B.

Re: how to Maintain static total & sub total value in pivot table irrespective of the selection

andrey_krylov — Sun, 16 Sep 2018 16:18:40 GMT

Hi, Shwetha. I think dimensionality() is what you need. And expression is like this

If(dimensionality() = 1, Sum({< Dim2, Dim3 >} Fact1),

If(dimensionality() = 2, Sum({< Dim1, Dim3 >} Fact1),

Sum(Fact1)))

Re: how to Maintain static total & sub total value in pivot table irrespective of the selection

qlikviewwizard — Mon, 17 Sep 2018 01:16:36 GMT

Hi,

Please check this.

How to use - Dimensionality()

Re: how to Maintain static total & sub total value in pivot table irrespective of the selection

shwethaa — Mon, 17 Sep 2018 06:14:57 GMT

I tried this, But Problem is when i select particular dimension Pivot table display for all values in that dimension .

Only for select dimension value it shows fact value and for remaining it is 0.

Any way to display selected value?

Like if i select AB in Dim and AC in Dim3 then it should display 1 row and total for Dim1 is 21 and Dim2 is 11 for Fact1

Re: how to Maintain static total & sub total value in pivot table irrespective of the selection

shwethaa — Mon, 17 Sep 2018 06:16:24 GMT

Thanks for solution can you please help me to change pivot Dimension Values based on selection.

Re: how to Maintain static total & sub total value in pivot table irrespective of the selection

andrey_krylov — Mon, 17 Sep 2018 06:38:18 GMT

Can you share a sample, Shwetha?