topic Re: Incorrect Total Sum in Pivot Table in QlikView

Incorrect Total Sum in Pivot Table

Fri, 13 Dec 2013 10:19:29 GMT

Hello,

I used to sum calculated negative results in a Pivot Table and the grand total at the end of the table is incorrect. Once I switch to straight table and set the properties to summarize "of rows", I'm fine. Unfortunately this setting is not available in my Pivot Table.

I used to try it with set analysis, but I don't think this might help.

Example:

1) I have different calculated results of value 'x' as starting situation -> sum ( x )

sum ( x ) returns positive and negative Values.

2) I want to sum only negative results of 'sum( x )' so I tried the simple way

'if (sum( x ) < 0, sum ( x ), 0)'

The results of the rows in my table are correct, BUT the grand total is incorrect. The grand total is the sum of all results (negative + positive). It doesn't matter if I try to use additional aggregation in the formula

All other solution approaches didn't help so far. There might be a very simple solution... Thanks for any hint!

Re: Incorrect Total Sum in Pivot Table

Gysbert_Wassenaar — Fri, 13 Dec 2013 10:57:50 GMT

Try putting a sum(aggr( ...expression... , DimA, DimX)) around your expression where DimA, DimX are the dimensions of your pivot table.

And you can use rangemax(sum(x),0) instead of the if statement.

Re: Incorrect Total Sum in Pivot Table

Fri, 13 Dec 2013 11:15:42 GMT

Hello Gysbert,

thanks for your reply! I think you mean 'rangemin', because I want to sum all negative results...

I already tried to do it with aggregation. Now the formula is

=RangeMin (sum (aggr(sum({$<Finanzkonto = {"*"}>} Saldo), Mandant, Kontonamelang)),0)

and the result is still the same... grand total is incorrect, s. below.

Frustrating...

Re: Re: Incorrect Total Sum in Pivot Table

Gysbert_Wassenaar — Fri, 13 Dec 2013 11:20:57 GMT

I meant something like this:

sum (aggr(rangemin(sum({$<Finanzkonto = {"*"}>} Saldo),0), Mandant, Kontonamelang))

If it doesn't work can you post an example document that demonstrates the problem?

Re: Re: Incorrect Total Sum in Pivot Table

Fri, 13 Dec 2013 11:42:24 GMT

That's it! I have to spend more time with range-functions... Great! Thank you very much!!

Re: Incorrect Total Sum in Pivot Table

Wed, 05 Feb 2014 18:50:43 GMT

Hey guys,

I am given a task to calculate the frequency of calls across a territory. If the rep called a physician regarding the sale of the product 5 times, then frequency is 5 and HCP count is 1....I generated frequencies from 1 to 124 in my pivot table using a calculated dimension which is working fine. But my concern is :

My manager wants frequencies till 19 in order from 1..2..3..4...5..6.....19...

And from the frequency 21-124 as 20+.

I would be grateful if someone helps me with this.....Eager for the reply....

Re: Incorrect Total Sum in Pivot Table

stefano27 — Wed, 27 Jul 2022 21:12:19 GMT

Thank you. The solution above with the RangeMin worked for me as well.

Re: Re: Incorrect Total Sum in Pivot Table

stefano27 — Wed, 27 Jul 2022 21:12:56 GMT

Thank you. This worked for me as well.