Rolling Average - Including 0 in Average

[Anonymous]

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

[JonnyPoole]

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.