Sum of dimensions (aggr) in pivot table

[#1]

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

[#2]

The distinct solved the zero problem but it now makes a calculation that's not correct:

with the old expression:

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

Now with distinct added to the expression I get these values:

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

...

[#3]

A*B/10000 is the same as (A*B)/10000 ? So that does not change anything.

It is correct in a mathematical way, it just gives me something wrong for some of the values..

Alle the values used in the calculation are shown in the cart.

And I have to divide it by 2 or it gives me a wrong aswer..

Check the others:

60*125/10000*10*2=15, which is correct.

If I do not divide by 2, it shows me 30, view below:

I do not know why it does it, but dividing it by 2 solves it anyway..

[#4 · Accepted solution]

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

[er_mohit]

thumsup

So,Please Closed this thread to mark it as either helpful or correct answer so that it might be helpful for others developer

Regards

Mohit