topic Re: Fixing total in pivot in QlikView

Fixing total in pivot

Wed, 15 Feb 2012 12:05:17 GMT

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

Fixing total in pivot

stephencredmond — Wed, 15 Feb 2012 12:55:23 GMT

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

Re: Fixing total in pivot

Wed, 15 Feb 2012 14:00:45 GMT

Excellent!

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

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

Re: Fixing total in pivot

Wed, 15 Feb 2012 14:59:42 GMT

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.