10 Replies Latest reply on Aug 30, 2013 11:19 AM by Jim Wahl

    Moving average calculation

    Cristina Palo

      Hello everyone:

       

      This is my challege this week:

       

      I am trying to get a moving average to calculate my baseline sales. I have used the following formula to achieve that:

       

      WINDOW_AVG(SUM([Total Sales Amount]), -4, 4)

       

      However I want to exclude the weeks which we ran an ad - so it would trully give me a BASELINE. I have second table with only five records which has the information on the 5 ads we ran during the year.

       

      If the "date" is not present in the AD TABLE I would like the WINDOW_AVG(SUM([Total Sales Amount]), -4, 4) just to work as is, but if the date IS on the AD TABLE; I would like the expression above to exclude that value.

       

      I am not very good at combining expressions - any help would be appreciated!

       

      Thanks,

       

      Cristina