1 Reply Latest reply: Sep 29, 2014 4:37 PM by Jonathan Poole RSS

    Rolling Average - Including 0 in Average



      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:


      WeekSalesRolling Average of Sales (What I am trying to achieve)Rolling Average( What expression gives me)



      594.512 (Avg of 15 and 9)


      Any help would be greatly appreciated.


      Thank you,


        • Re: Rolling Average - Including 0 in Average
          Jonathan Poole

          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.