Rolling 4 week average

[nnleiker]

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

[chrismarlow]

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.

[Brett_Bleess]

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