How to calculate AVERAGE 4 rolling period sales

[nguyenviethung]

Hi all bro,

I am a newbie and trying to build a BI report. Now I get stuck at this point and hope you guys can help me.

I got a huge sales table by day; like this:

Date	SKU	SALES
1/1/2018	A	2
1/6/2018	B	4
1/7/2018	A	6
1/13/2018	A	8
1/15/2018	A	10
1/16/2018	B	12
1/21/2018	A	14
1/27/2018	A	16
1/28/2018	B	18
1/29/2018	A	20
2/3/2018	B	22
2/4/2018	A	24
2/9/2018	B	26
2/10/2018	A	28
2/11/2018	B	30
2/16/2018	A	32
2/17/2018	B	34
2/18/2018	A	36
2/19/2018	B	38
2/24/2018	A	40
2/27/2018	B	42
2/28/2018	A	44

Qlik Sense will generate a Week dimension based on Date.

Now what i want is a measurement of average last 4 week SALES like this result table:

Date.autoCalendar.Week	Sum(SALES)	Avg last 4 week SALES
W1	6	-
W2	14	6
W3	22	10
W4	30	14
W5	60	18
W6	78	31.5
W7	96	47.5
W8	114	66
W9	86	87
Grand Total	506

Bro, please help me to go ahead. Thank you all.

[Chanty4u]

Have a look

Calculate an average for the last 4 weeks

[isingh30]

May be this -

[PrashantSangle]

Hi,

try below expression in straight table

RangeSum (Above(sum(SALES),0,4))/if(RowNo()>3,4,RowNo())

Regards,

[nguyenviethung · Accepted solution]

‌Thanks bro, it’s nearly correct as your solution calculate the current week also; but I want only 4 previous weeks.

Base on your solution; I searched and found out the correct answer for what i want:

rangeavg(above(sum(SALES),1,4)

Thanks again.

[nguyenviethung]

‌thanks bro; but i wanted a different thing