Caluculation per item with dimension year

[michael_maeuser]

Hello,

I have the following expression for my raw material:

=(Sum(Raw Material])/[Product.Lot Size]/Count( DISTINCT [Prod_ Order No_]))*Quantity

If I have item as dimension, it works fine. But now I want year as dimension. I tried it with aggr but I don´t get it to work correctly.

Thanks for helping!

[Anonymous]

That's because you have several Product.Lot Size's and Quantity's for a year.

Not even working if you aggregate the whole thing over item and sum up the parts?

In that case maybe you can upload a small example?

[michael_maeuser]

the lot size for one item is always the same (comes from item card). ok, it was sum(Quantity). But still i don´t get a result. i tried =(aggr(Sum(Raw Material]/[Product.Lot Size]), item)/aggr(Count( DISTINCT [Prod_ Order No_]), item))*aggr(sum(Quantity), item)

i´m also not sure how to put aggregate over item for the lotsize, since there is now sum or anything else.

[michael_maeuser]

so now attached is a sample qvw. This issue is very important to me, so thanks for any help.

[sivarajs]

Check your expression is correct or not?

see the attached sample

[michael_maeuser]

no it´s incorrect. the lotsize comes from the itemcard and is not related to a date at all. next problem is even though my dimension is year, qlikview needs to execute the calculation per item and then show me the sum. so i probably need aggr function or s.th. like that. but i don´t know how to that exactly

[#7]

I don't understand why item does not appear as a dimension in your per year table

[michael_maeuser]

because i want to show the total of rawmaterial costs per year