Weighted average and trend

[#1]

Hi,

I am using the expression below to calculate the trend of prise erosion (last year weighted average price over actual year weighted average price.

(
(sum({$<Year={'2015'}>} aggr( NODISTINCT avg({$<Year={'2015'}>}Pricing_LocalTotal) * (sum({$<Year={'2015'}>} PlannedQty)+ Sum({$<Year={'2015'}>}Quantity)), FullMonth))/ aggr(noDISTINCT (sum({$<Year={'2015'}>}PlannedQty)+ Sum({$<Year={'2015'}>}Quantity)), ItemCode))

-
(
sum({$<Year={'2014'}>} aggr ( noDISTINCT avg({$<Year={'2014'}}>}Pricing_LocalTotal)* (sum({$<Year={'2014'}>} PlannedQty)+ Sum({$<Year={'2014'}>}Quantity)), FullMonth))/ aggr(noDISTINCT (sum({$<Year={'2014'}>}PlannedQty)+ Sum({$<Year={'2014'}>}Quantity)), ItemCode))
)
/
(
sum({$<Year={'2014'}>} aggr ( noDISTINCT avg({$<Year={'2014'}>}Pricing_LocalTotal)* (sum({$<Year={'2014'}>} PlannedQty)+ Sum({$<Year={'2014'}>}Quantity)), FullMonth))/ aggr(noDISTINCT (sum({$<Year={'2014'}>}PlannedQty)+ Sum({$<Year={'2014'}>}Quantity)), ItemCode))

It works fine as long as I display a single value but the need is to display a line chart over month of this trend. I cannot figure out why it fails trying this.

Do you have any clue ?

Thanks,

Bertrand

[#2]

Hi Bertrand

i'm guessing "full month" is has distinct year and month in it, eg March-14. And I take it the 2015 data is all in there?

Is the Line chart dimension full month also? the issue will because for each row, you are reporting what that metric is for that month in that year. so only the 2014 calculation will apply and not the 2015.

EG for March 14:

you will essentially get:

(0 (2015 part of function) + X) / X

Try using straight month for the dimension, does this work?

Erica

[#3]

Thanks for your answer. I tried using a straight month but it didn't solve the issue. I finally decided to calculate the weighted average in the loading script so I am now able to draw the line. But it decreases the flexibility of navigation and erase drill-down possibilities.

Thank you for your contribution anyway.

Have a good WE