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: 
claudialet
Contributor III
Contributor III

How to calculate dimension total in pivot table ?

Hi all

Here is are my columns and measures in a Pivot table

Retailer  :             Apple                   Verizon                       Sprint 

                            50                           50                                50         

( Should show subtracted total from collapsed dimension below)

Sales:        + online    +store             + online    +store             + online   + store

                         20         70                  20        70                   20         70

Instead  it keeps showing 70.

  How do I write expression to show subtracted total  instead?

6 Replies
dplr-rn
Partner - Master III
Partner - Master III

try below link approach

Missing Manual - Before() and After()

amit_gupta
Contributor III
Contributor III

Claudia,

Just to revarify, you are expecting result like this:

Test 2.png

and data is something like:

 

RetailerSalesBy
Apple20Online
Apple30Store
Verizon20Online
Sprint20Online
Verizon30Store
Sprint30Store

Please correct me, if i am wrong.

claudialet
Contributor III
Contributor III
Author

Yes but you are totaling  20 + 30

My data is  -70  and -20  and I need to show  - 50. Instead it keeps showing - 70

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You can use the Dimensionality() function to detect that you are on the Total row and perform a different calculation. Dimensionality() = 1 for the total row

Something like:

if(Dimensionality()=1,  do total calc, sum(Sales))

-Rob

claudialet
Contributor III
Contributor III
Author

This is not a total row.

Collapsed  pivot shows :  Online sales values :  -70   and -20   the calc here is  min ( Online Sales)  - this is the only way it shows the correct values not sum

Closed pivot  should shows :       -50  on top

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Ok, so I misused the term "Total".  Total rows have Dimensionality()=0.  But the first Dimension (Retailer) will have Dimensionality()=1.  Add "Dimensionality()" as a Measure to your table to see how it works

-Rob