Solved: Re: Sum of dimensions (aggr) in pivot table - Qlik Community

Report Inappropriate Content · ‎2013-05-30

Hi guys,

Question concerning the aggregation function.

I have 5 dimensions and 2 expressions.

Dimensions

*name customer

*article

*pieces per pack

*length

*width

Expression:

sum(NumberSold)

I need the to calculate:

pieces per pack * length * width * sum(NumberSold)

It is a pivot table, so I need the sum for each customer, but this is what qlikview does not want to show.

I am currently using the following expression for the last column:

=aggr
(
((max(length)*max(width))/1000)*max(pcsperpack), Klantnaam
)

But I am using this as an expression, and last time I was using it as a calculated dimension. I am a bit lost at the minute.. As you can see, the sum(NumberSold) is not even yet in the expression because I do not now where to put it..

Do I need a calculated dimension?

And which expression do I need for the last column?

Thanks a lot!!

Zipke

Report Inappropriate Content · ‎2013-05-31

The expression you have given me gives me '-' everywhere..

But...

I have got it, I have got it!

For some reason it works.

This was what I originally started with and it did not give me the total for some customers

=LENGTH*WIDTH/10000*Pcsperpack*sum(NumberSold)

And I changed it to this:

=sum(Pcsperpack*WIDTH*LENGTH/10000*NumberSold)

And this works!!

Since it did not give me the sum for some of the customers, I thought I had to use aggr function, but apparently that's not even needed.. Good times!!

Thanks for all your effort and time though er.mohit!

Much appreciated!

Zipke

View solution in original post

vikasmahajan · ‎2013-05-30

Please try following expr

aggr(sum(pack * length * width * sum(NumberSold),Customer,article))

Hope this help.

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

Report Inappropriate Content · ‎2013-05-30

It tells me there is an error in my expression and it shows this comma red:

aggr(sum(pack * length * width * sum(NumberSold),Customer,article))

It gives me ''-' in my table..

Report Inappropriate Content · ‎2013-05-30

It tells me there is an error in my expression, and the comma is red:

=

sum(aggr(sum([Pcsperpack]*LENGTH*WIDTH)*NumberSold),Customer, Article)

Still '-' as result.. The partial sum and the values are '-' at the minute

er_mohit · ‎2013-05-30

i don't know how it gives you error expression check your field name also

try this way

sum(aggr(sum([Pcsperpack]*LENGTH*WIDTH)*NumberSold,Customer, Article))

tRY THIS

Report Inappropriate Content · ‎2013-05-30

Yes it works !!!!! The brackets solved the problem!

Thanks a lot er.mohit!

Oh yer, had a question though:

why are Customer and Article at the end of my expression?

The colums before the partial sum? Trying to understand the expression

Report Inappropriate Content · ‎2013-05-30

If I swap article with ArticleType (same column, just another dimension).

Do I need to use the following expression then:

sum(aggr(sum([Pcsperpack]*LENGTH*WIDTH)*NumberSold,Customer, ArticleType))

And then in presentation tick Partial sum of ArticleType, because when I do this, it gives me wrong numbers..

So guess I do not really understand the aggr expression so far..

Hope you want to explain it to me..

Thanks!

Report Inappropriate Content · ‎2013-05-30

Ok,

Not completely solved apparently

Why are some of them 0??

Report Inappropriate Content · ‎2013-05-30

Is this what I have to use?

=sum(aggr(sum(sum([Pcsperpack])*sum(LENGTH)*sum(WIDTH)/10000)*NumberSold,Customer, Artikelnr))

Or what do you mean?

Thanks

Report Inappropriate Content · ‎2013-05-30

The problem is not solved with the expression you quoted

=sum(aggr(sum(sum([Pcsperpack])*sum(LENGTH)*sum(WIDTH)/10000)*NumberSold,Customer, Artikelnr))

I was closest to the right answer with the expression:

(sum(aggr(sum([Pcsperpack]*LENGTH*WIDTH)*NumberSold,Customer, Article)))/2

But it then gave me the weird values, the 0's that are not meant to be zero (view the print screen above)

At the minute I do not know what to test anymore, I am lost..