Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Highlighted
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)))

Highlighted
Master II
Master II

Hi,

Please check this.

How to use - Dimensionality()

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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

Highlighted
Specialist
Specialist

Can you share a sample, Shwetha?