Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table with week, SKU, and sales during that week.
I am trying to take the rolling two week average of the sales.
I have the following expression:
sum(aggr(rangesum(above( sum(sales), 0, 2)), Type)/2)
It gives me the rolling average for weeks where there are sales but does not include weeks without sales. I want the rolling average of two weeks including the weeks where there were no sales.
For example, if I have the following table:
Week | Sales | Rolling Average of Sales (What I am trying to achieve) | Rolling Average( What expression gives me) |
---|---|---|---|
1 | 7 | 7 | 7 |
2 | 23 | 15 | 15 |
3 | 15 | 19 | 19 |
4 | 0 | 7.5 | 0 |
5 | 9 | 4.5 | 12 (Avg of 15 and 9) |
Any help would be greatly appreciated.
Thank you,
Isabel
I think it would work if your data source had '0' for week4 . But if its blank and the combination comes from a join from a fixed week dimension, then it could be being treated as missing or null...in which case null+ something else is always null (and being rendered as zero).
If you replace sum(Sales) with a check for null (and assign 0) does it help ?
sum(aggr(rangesum(above( if( isnull(sum(sales)) , 0, sum(Sales)) , 0, 2)), Type)/2)
A sample may help to give me a few attempts to solve.