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

    Rolling Average - Including 0 in Average

    Isabel Nowinowski

      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:

       

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

       

      7.5

      0
      594.512 (Avg of 15 and 9)

       

      Any help would be greatly appreciated.

       

      Thank you,

      Isabel

        • 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.