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!