Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum of dimensions (aggr) in pivot table

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)

Knipsel.JPG

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

1 Solution

Accepted Solutions
Not applicable
Author

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)

Knipsel.JPG

And I changed it to this:

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

And this works!!

Knipsel2.JPG

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

13 Replies
vikasmahajan

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.
Not applicable
Author

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..

Not applicable
Author

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
Master II
Master II

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

Not applicable
Author

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

Not applicable
Author

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!

Not applicable
Author

Ok,

Not completely solved apparently

Knipsel.JPG

Why are some of them 0??

Not applicable
Author

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

Not applicable
Author

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..