I have a requirement to calculate the price effect of the yearly average price of last year to the monthly average price of the current year. Following calculations are working:
- Average price current year for each month: Exception aggregation, sales by month / sales quantity by month
- Averge price last year for whole year: Exception aggregation, sales last year / sales quantity last year
- Average price last year for whole year: --> constant on calender year/month in query
- Delta price average price current year - average price last year for whole year (constant), calculated for each month current year
- Delta price * sales quantity current year (needed to use the Fix-operator in the formula)
The whole setup is working in the query - as long as calender year/month is in the drilldown.
As soon as I remove the drilldown on year/month the effect (delta price * sales quantity) shows 0. I think the example attached shows the problem quite well.
Thanks for any hint/comment.