Average using previous weeks data

[James_B]

Hi All, 

could someone please assist me.

I am trying to eliminate the manual process of me having to use this formula within an excel sheet (as seen in picture below)

I would like Qlik to generate a figure based on the average form the previous 3 weeks and would need this to be a rolling average.

Thanks in advance 

James 

[OmarBenSalem]

It would be something like : 

aggr(rangeavg(
above(
Avg({<Week,date>} total <Week> worked)
,1,3))
,Week)

What this does : 

Part 1 : Avg({<Week,date>} total <Week> worked) : this calculates the Avg worked for each week.

The RanegAvg .. 1,3) part does calculate the avg of the Part 1 (of each week) starting from the week abve (1) and doing an avg of 3 weeks (3)

Result :

Example : in week 5 : it calculates the rangeavg of the avg of (week 4+week 3 + week 2) = avg (2.6 + 2.4 + 3.6) = 2.87 !

and with this, even if u select a date in week 5, it would still show u the avg of the week 5 (week of the selected date) : 2;87

[James_B]

Hi Omar, 

Thank you for your reply but unfortunately this isn't working 

its producing the figure correctly 

Many thanks 

James 

these pictures show how excel work out the figure for me 

is it possible for Qlik to do the same to eliminate me having to do this in excel to populate my table?

Cheers 

James 

[OmarBenSalem]

What's not working? 

[James_B]

It is not calculating the figure correctly and filling in all the blanks