Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Please try following expr
aggr(sum(pack * length * width * sum(NumberSold),Customer,article))
Hope this help.
Vikas
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..
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
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
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
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!
Ok,
Not completely solved apparently
Why are some of them 0??
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
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..