Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shwethaa
Contributor III
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
Specialist
Specialist

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
Master II
Master II

Hi,

Please check this.

How to use - Dimensionality()

shwethaa
Contributor III
Contributor III
Author

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

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

andrey_krylov
Specialist
Specialist

Can you share a sample, Shwetha?