sum grossweight of each 'MultiplePurchaseOrder'

[Anonymous]

Hi Guys,

How can I do a sum of a speficy dimension instead of sum of all rows?

I tried: Aggr(sum([Gross Weight]), 'MultiplePurchaseOrder') but that didn't work in the barchart only in the tablechart :S.

The problem that i'm facing is that sum(grossWeight) gives me the sum of all the rows, rather than the sum of each 'MultiplePurchaseOrder'

The sum(Distinct GrossWeight) comes pretty close, however it can occur that different 'MultiplePurchaseOrder' happen to have the same grossweight, so that solution woudn't work.

Does anyone know how to tackle this problem?

I've attached a screenshot. The total sum should be:  4080 instead of 15986

Hope some one can help!

iSam

[swuehl · Accepted solution]

Maybe like

=sum(aggr( sum( distinct [Gross Weight]), RecNo))

or

=sum( aggr( sum( [Gross Weight]) / count(RecNo), RecNo))

[Anonymous]

yeeeeeey! Thank you so much Stefan! That works!!

I just have a question for my own understanding, since i'm still a qlikview newbie : Why did you use sum before ánd after the aggr statement? 

Thanks in advance!

iSam

[swuehl]

Well, the second sum() ist part of the expression you want to aggregate,

sum( distinct [Gross Weight])

You evaluate the expression for each RecNo. If RecNo is unambiguous, like in your table where you use it as dimensions, I think you don't necessarily need the first sum. But if RecNo is ambiguous, like in a Total line, you need the first sum to aggregate the single RecNo expression values correctly.

[Anonymous]

Thanks Stefan! That makes sense now