Qlik Community

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

1 Solution

Accepted Solutions
Luminary
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

View solution in original post

3 Replies
Luminary
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

View solution in original post

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))

Highlighted
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.