
Re: Volume and PriceVariance with multiple unitcosts
Sunny Talwar Sep 7, 2016 9:33 AM (in response to Peter Rieper)Hi Peter 
It would be somewhat difficult to provide a complex expression without testing it out a little. Would you be able to provide a sample where you have been working on this? May be we can play around with it a little before we propose anything:)

Re: Volume and PriceVariance with multiple unitcosts
Sunny Talwar Sep 7, 2016 4:02 PM (in response to Peter Rieper)Not sure if this is right because the numbers don't match, but can you check
Expression:
Aggr(
If(SUM(Q2)  SUM(Q1) > 0, (SUM(Q2)  SUM(Q1)) * SUM(C1) / SUM(Q1),
If(Sum(TOTAL <Group> Q2)  Sum(TOTAL <Group> Q1) > 0, (Sum(TOTAL <Group> Q2)  Sum(TOTAL <Group> Q1)) * (Sum(C1)/Sum(TOTAL <Group> Q1)),
(Sum(TOTAL Q2)  Sum(TOTAL Q1)) * (Sum(C1)/Sum(TOTAL Q1))
)), City, Month, Item, Group)

VolumePrice Example.qvw 157.0 K

Re: Volume and PriceVariance with multiple unitcosts
Peter Rieper Sep 8, 2016 4:26 AM (in response to Sunny Talwar )Hi Sunny,
thanks for the feedback.
What needs to be done to have the calculation working, if only 03 or T3 is selected?
Peter

Re: Volume and PriceVariance with multiple unitcosts
Peter Rieper Sep 8, 2016 4:54 AM (in response to Peter Rieper)Thanks Sunny for helping towards the correct solution.
Finally have used:
SUM(AGGR(
IF(SUM(Q2)  SUM(Q1) > 0, (SUM(Q2)  SUM(Q1)) * SUM(C1) / SUM(Q1),
IF(SUM({$<Item =, Group = >} TOTAL <Group> Q2)  SUM({$<Item =, Group = >} TOTAL <Group> Q1) > 0, (SUM({$<Item =, Group = >} TOTAL <Group> Q2)  SUM({$<Item =, Group = >} TOTAL <Group> Q1)) * (SUM({$<Item =, Group = >} C1) / SUM({$<Item =, Group = >} TOTAL <Group> Q1)),
(SUM({$<Item =, Group = >} TOTAL Q2)  SUM({$<Item =, Group = >} TOTAL Q1)) * (SUM({$<Item =, Group = >}C1)/SUM({$<Item =, Group = >} TOTAL Q1))
))
, City, Month, Item, Group)
)Regards Peter

Re: Volume and PriceVariance with multiple unitcosts
Sunny Talwar Sep 8, 2016 4:59 AM (in response to Peter Rieper)Awesome, that was going to be my next suggestion. I was not sure if what we have today was correct or not because it did not completely match what you wanted as the result. But I am glad it all worked out at the end.
Best,
Sunny

Re: Volume and PriceVariance with multiple unitcosts
Peter Rieper Sep 8, 2016 6:26 AM (in response to Sunny Talwar )Hi Sunny,
I made a mistake in the underlying calculation in Excel  sorry. Will correct my initial post.
Peter



