Hi all -
Trying to get a weighted moving average of charges set up and am not having any luck.
Consider this data-set
| POST_DATE | DETAIL_TYPE | AMOUNT |
| 1/1/2016 | 1 | 20 |
| 1/2/2016 | 1 | 30 |
| 2/3/2016 | 1 | 20 |
| 2/4/2016 | 1 | 20 |
| 3/5/2016 | 1 | 35 |
| 3/6/2016 | 10 | -30 |
| 4/7/2016 | 1 | 30 |
| 4/8/2016 | 1 | 21 |
| 5/9/2016 | 1 | 15 |
| 5/10/2016 | 10 | -40 |
| 6/11/2016 | 1 | 20 |
| 6/12/2016 | 1 | 30 |
| 7/13/2016 | 1 | 20 |
| 7/14/2016 | 1 | 60 |
| 8/15/2016 | 1 | 10 |
| 8/16/2016 | 10 | -100 |
| 9/17/2016 | 10 | -10 |
| 10/18/2016 | 1 | 20 |
| 10/19/2016 | 1 | 25 |
| 11/20/2016 | 10 | -30 |
| 11/21/2016 | 1 | 20 |
| 12/22/2016 | 10 | -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))
