Moving Averages in Line Chart

[odassier]

Hi all,

I am trying to show a daily stock price line chart along with two moving averages, 20-day and 60-Day ( so the chart has three lines).  I have daily data going back to 2000 but want the user to be able to select the chart start and end dates and the moving averages should be computed on the full data set but only displayed for the selected dates.

I am using this expression for the 60-day: RangeAvg(Above(RangeSum(Above(Sum(VALUE),0,Rowno(Total))),1,60))

The issue I am having is that if I select say just 2019 as dates for the line chart, the expression recomputes the moving average from Jan 2019 so both the 60-Day and the 20-Day moving average lines overlap each other for the first 20 days, and both start form the first daily value, which is not what I want.  I want to know on the first day of the chart, what was the average stock price for the previous 20 and 60-days, not for the average to start being computed on the first day of the selected date range for the chart (i.e. calculation of moving average needs to be independent from chart date selection).

Is there a way to modify the above expression so that the first data point on the chart is based on prior days and not limited to the data from the date selection?

Thanks

[saminea_ANZ]

Perhaps this?

If(GetSelect(Year)>0, RangeAvg(Above(RangeSum(Above(Sum(VALUE),0,Rowno(Total))),1,60)), RangeSum(Above(Sum(VALUE),0,Rowno(Total))))

[odassier]

Hi Saminea,

thanks for helping out.  Unfortunately, "GetSelect" isn't an acceptable function in Qlik Sense (so it tells me).  But I tried this:

If(([DT-Date.autoCalendar.Year])>0, RangeAvg(Above(RangeSum(Above(Sum(VALUE),0,Rowno(Total))),1,60)), RangeSum(Above(Sum(VALUE),0,Rowno(Total))))

and it gives me the same thing I had (i.e. does not solve my problem).  But maybe I'm doing it wrong?

Olivier

[saminea_ANZ]

My bad, use this

If(GetSelectCount(Year)>0, RangeAvg(Above(RangeSum(Above(Sum(VALUE),0,Rowno(Total))),1,60)), RangeSum(Above(Sum(VALUE),0,Rowno(Total))))

[odassier]

Unfortunately, I get the same behaviour with this expression (see attached screenshot). The averages are all recomputed form the start date of the chart rather than the using the previous 60 and 200 days prior to the chart start date selection.

Olivier