# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

## Fixing total in pivot

Hi!

I have a pivot chart where I show gross, refunds and net (with partial sums). No problem there.

As dimension I use Group which values are gross, refund and net. Net is calculated as gross - refund.

The problem occur when the grand total is calculated. I really just want to show the net.

Straight table is NOT the answer, I have multiple dimensions.

Found a solution where you create a assosiative table and somehow use that with aggregated values for dimension and then use the fact table for (pivot)total.

But I can't get it in my head how to create that table. What to use as key?

Really would like to be able to control what goes in the, grand, total with set analysis or something.

Suggestions?

Regards

thomas

Tags (8)
1 Solution

Accepted Solutions
Highlighted
Luminary

## Fixing total in pivot

Hi Thomas,

You could using an If statement in your expression using the Dimensionality() function.  I believe that 0 is the grand total:

if(Dimensionality()=0, Null(), Sum(Myfield))

Regards,

Stephen

3 Replies
Highlighted
Luminary

## Fixing total in pivot

Hi Thomas,

You could using an If statement in your expression using the Dimensionality() function.  I believe that 0 is the grand total:

if(Dimensionality()=0, Null(), Sum(Myfield))

Regards,

Stephen

Highlighted
Not applicable

## Re: Fixing total in pivot

Excellent!

With Null() the total row dissapears but with a set analysis expression it works.

`if(Dimensionality()=0, Sum({<Grupp={Net}>}Myfield)), Sum(Myfield))`
Not applicable

## Re: Fixing total in pivot

Hmm, one issue is that if you do a selection (lets say Gross) the grand total still calculates according to set analysis.

Maybe I can solve that with another if that eliminate the grand total if any selection is made. The sub total is still there and that should be enaugh.