Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

shwethaa
New Contributor III

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

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:

Dim1Dim2Dim3Fact1Fact2
A total--2144
AAB Total -1124
AABAC612
AABAD512

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.

5 Replies
andrey_krylov
Contributor III

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

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

Arjunarao
Honored Contributor II

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

Hi,

Please check this.

How to use - Dimensionality()

shwethaa
New Contributor III

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

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
New Contributor III

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

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

andrey_krylov
Contributor III

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

Can you share a sample, Shwetha?