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

[shwethaa]

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.

[andrey_krylov]

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)))

[qlikviewwizard]

Hi,

Please check this.

How to use - Dimensionality()

[shwethaa]

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

[shwethaa]

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

[andrey_krylov]

Can you share a sample, Shwetha?