topic Partial Sum in Pivot Table in QlikView

Partial Sum in Pivot Table

Thu, 27 May 2010 15:11:36 GMT

Hello everybody,

I have the following formula in a pivot table

=if((sum((if((not Auftragsart='RE') and
(not Auftragsart='ZRE') and
(not Bundle = 'K') and
(len([Absagegrund #]) < 2) and
(not Auftrag = '0100448925') and
(not Auftrag = '0100506496') and
(not Auftrag = '0100458473') and
([Jahr(%Datum)] > 2006),1,0) * Auftragsmenge)))=0,null(),
(sum(Auftragsmenge*[VK-Preis]) + sum(Auftragsmenge*Handling)))

the sum for each position is correct. But the total sum calculate all values, even the values that are eliminate with the If-clause.

I hope my explanation was not to abstract. [:)]

Tanja

Partial Sum in Pivot Table

Thu, 27 May 2010 16:03:37 GMT

Hi Tanja

You got for sure a lengthy condition list.

I assume for the total calculation your Auftragsmenge is > 0 and so everything gets counted.

Can you add a flag 0/1 during the data load (or qvd creation) representing all your conditions.

You can then simply multiply Auftragsmenge with this flag to eliminate the unwanted values and this should then also work with your total.

You could of course also duplicate all conditions for the 2 Auftragsmenge values but that will make it even more cluttered.

Regards

Jürg

Partial Sum in Pivot Table

johnw — Sat, 29 May 2010 00:55:42 GMT

A general solution to this kind of problem is this:

sum(aggr(your current expression, your chart dimensions))

Each individual row is already aggregated by your chart dimensions, so this has no effect at the row level. It only has an affect on partial sums, where it is explicitly applying your expression to every row instead of applying your expression on the total line.

Partial Sum in Pivot Table

Sat, 29 May 2010 09:06:29 GMT

looking at your formula, would not also this give you the correct result?

=sum(if((not Auftragsart='RE') and
(not Auftragsart='ZRE') and
(not Bundle = 'K') and
(len([Absagegrund #]) < 2) and
(not Auftrag = '0100448925') and
(not Auftrag = '0100506496') and
(not Auftrag = '0100458473') and
([Jahr(%Datum)] > 2006), Auftragsmenge * ([VK-Preis]+Handling) ))

Juerg

Partial Sum in Pivot Table

Anonymous — Thu, 09 Dec 2010 15:12:41 GMT

Hi John I am doing what you said above. please see the snapshot below

My question is why its not summing and why i am not getting the partial sum ?

also in the 5th column, why I am not getting the partial sum ? Please help me with this its quite urgent.

Much appreciated.

Waqas

Partial Sum in Pivot Table

Thu, 09 Dec 2010 15:59:19 GMT

ojo con los () Prueba

sum(aggr(your current expression, your chart dimensions))

sum(aggr(F4, F1,F2))

Suerte

Partial Sum in Pivot Table

Anonymous — Thu, 09 Dec 2010 17:07:52 GMT

Hi Suerte

Thanks for your promt reply.

Please see below the snapshot. It works fine for F3 but not for F4.

Any ideas?

Partial Sum in Pivot Table

johnw — Fri, 10 Dec 2010 20:27:37 GMT

I'd think this?

sum(aggr(sum(F4),F1,F2))

If there is more than one value for F4 for a given combination of F1 and F2, merely saying F4 would return null. You want the sum, so you'd want sum(F4) inside the aggr(), I'd think.