1 Reply Latest reply: Feb 14, 2017 11:08 AM by Gysbert Wassenaar RSS

    Weighted Moving Average (Not working)

    Kevin McKeown

      Hi all -

       

      Trying to get a weighted moving average of charges set up and am not having any luck.

       

      Consider this data-set 

      POST_DATEDETAIL_TYPEAMOUNT
      1/1/2016120
      1/2/2016130
      2/3/2016120
      2/4/2016120
      3/5/2016135
      3/6/201610-30
      4/7/2016130
      4/8/2016121
      5/9/2016115
      5/10/201610-40
      6/11/2016120
      6/12/2016130
      7/13/2016120
      7/14/2016160
      8/15/2016110
      8/16/201610-100
      9/17/201610-10
      10/18/2016120
      10/19/2016125
      11/20/201610-30
      11/21/2016120
      12/22/201610-15

       

      I have a MasterCalendar set up to get a MonthYear timestamp based on POST_DATE.

      I have a Master Item measure that attempts to calculate a Weighted Moving Average using a 6/3/1 model. My measure doesn't show any errors, but when I make a bar graph with MonthYear as the dimension and the WMA as the measure, I get zeros across the whole graph. No idea why so any help would be appreciated.

       

      (sum(if(Month(AddMonths(MonthYearTX,-1,1))=Month(POST_DATE) and Year(AddMonths(MonthYearTX,-1,1))=Year(POST_DATE) and (DETAIL_TYPE=1 or DETAIL_TYPE=10),AMOUNT))*(.6))

      +

      (sum(if(Month(AddMonths(MonthYearTX,-2,1))=Month(POST_DATE) and Year(AddMonths(MonthYearTX,-2,1))=Year(POST_DATE) and (DETAIL_TYPE=1 or DETAIL_TYPE=10),AMOUNT))*(.3))

      +

      (sum(if(Month(AddMonths(MonthYearTX,-3,1))=Month(POST_DATE) and Year(AddMonths(MonthYearTX,-3,1))=Year(POST_DATE) and (DETAIL_TYPE=1 or DETAIL_TYPE=10),AMOUNT))*(.1))