Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Weighted Moving Average (Not working)

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))

1 Reply
Highlighted
MVP & Luminary
MVP & Luminary

Have a look at this document: Calculating rolling n-period totals, averages or other aggregations


talk is cheap, supply exceeds demand