Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
claudialet
New 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
dilipranjith
Valued Contributor III

Re: How to calculate dimension total in pivot table ?

try below link approach

Missing Manual - Before() and After()

amit_gupta
New Contributor III

Re: How to calculate dimension total in pivot table ?

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

Re: How to calculate dimension total in pivot table ?

Yes but you are totaling  20 + 30

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

MVP & Luminary
MVP & Luminary

Re: How to calculate dimension total in pivot table ?

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

Highlighted
claudialet
New Contributor III

Re: How to calculate dimension total in pivot table ?

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

MVP & Luminary
MVP & Luminary

Re: How to calculate dimension total in pivot table ?

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