
Price variance calculation with aggr and set analysis
AngelaEcheverri56 Jun 2, 2011 5:19 PM (in response to DanielFunke)It is really helpfull example..
do you know how can i do it by date in insted of year?
thanks
AE

Price variance calculation with aggr and set analysis
Trent Jones Jun 2, 2011 5:56 PM (in response to DanielFunke)Well the reason the simplified version works is because you have the total mode set on "Sum of Rows" whereas in the incorrect version you have it set on "Expression Total". To be honest I really don't understand what you're trying to do so I can't really be more helpful but I don't think just summing the rows is really what you want (it works fine now because it's 100 and 0, but I have a feeling that it isn't going to do what you want in a different situation).

Price variance calculation with aggr and set analysis
DanielFunke Jun 6, 2011 3:44 AM (in response to Trent Jones)Trent, you are right, the "simplified" version works only for this example and cannot be used to calculate percentage variances.
The idea is to analyse sales variances over time. Basically, because sales = sales price * volume, the sales variance can be caused by price variances or volume variances. The tricky thing is, that both variances can have opposite signs (usually lower prices cause higher volumes, but does this lead to higher sales?)


Re: Price variance calculation with aggr and set analysis
Rhonda Morton Jan 21, 2015 10:37 AM (in response to DanielFunke)I have been trying to do a very similar calculation and with your .qvw I was able to get it to work and wanted to share the equation I developed around the aggr function. When I created an expression with this calculation, I could even remove the "Product" dimension and it would still calculate correctly. Also, tested that if I wanted the key to be productcustomer, I could just add a comma, after product and add Customer and that would work also. Rhonda
=Sum(aggr(
(sum({$<year={$(=max(year))}>} Sales)/sum({$<year={$(=max(year))}>} QTY)
sum({$<year={$(=max(year)1)}>} Sales)/sum({$<year={$(=max(year)1)}>} QTY))
*sum({$<year={$(=max(year)1)}>} QTY),product))