Qlik Community

Qlik Sense App Development

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

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

Re: Weighted Moving Average (Not working)

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


talk is cheap, supply exceeds demand
Community Browser