Partial Sum in Pivot Table

[#1]

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

[#2]

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

[johnw]

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.

[#4]

Hi

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

[Anonymous]

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

[#6]

ojo con los () Prueba

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

sum(aggr(F4, F1,F2))

Suerte

[Anonymous]

Hi Suerte

Thanks for your promt reply.

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

Any ideas?

[johnw]

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.