topic Re: Rolling 4 week average in QlikView

Rolling 4 week average

nnleiker — Sat, 16 Nov 2024 20:03:00 GMT

Hi everyone

I am working on the calculation to get rolling average of 4 weeks ( starting fiscal week 1).

In my data set, it contains calendar week and fiscal week.

My calculation is simple: RangeAvg(above(sum([Value USD]),0, 4))

The problem here is that I want to plot the line chart with fiscal week as X axis, but the average of first 4 weeks based on 4 previous weeks. (see the attached sample qvw)

Is there any way to get include these 4 previous weeks to the calculation without showing the week (in this case fiscal week-1, fiscalweek-2, fiscalweek-3 in the line chart?

To be exact on the chart the value of starting point is 883 359 (USD), but I want to get 600 171 (USD).

Thanks for your help in advance

Re: Rolling 4 week average

chrismarlow — Thu, 19 Sep 2019 19:53:36 GMT

Hi,

I think there is a solution in here Calculating-rolling-n-period-totals-averages-or-other

So something like;

avg({<[Fiscal Week]={">3"}>}aggr(rangeavg(above(total avg({<[Fiscal Week]=>}[Value USD]),0,4)),[Fiscal Week]))

I would attach images, but seems I can't add them to this post 😞

Hope that helps,

Cheers,

Chris.

Re: Rolling 4 week average

Brett_Bleess — Mon, 30 Sep 2019 20:30:52 GMT

Have a look at the following Design Blog post as well, believe it may be of some help:

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

Regards,
Brett