Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nguyenviethung
New Contributor III

How to calculate AVERAGE 4 rolling period sales

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:

DateSKUSALES
1/1/2018A2
1/6/2018B4
1/7/2018A6
1/13/2018A8
1/15/2018A10
1/16/2018B12
1/21/2018A14
1/27/2018A16
1/28/2018B18
1/29/2018A20
2/3/2018B22
2/4/2018A24
2/9/2018B26
2/10/2018A28
2/11/2018B30
2/16/2018A32
2/17/2018B34
2/18/2018A36
2/19/2018B38
2/24/2018A40
2/27/2018B42
2/28/2018A44

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.WeekSum(SALES)Avg last 4 week SALES
W16-
W2146
W32210
W43014
W56018
W67831.5
W79647.5
W811466
W98687
Grand Total506

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

1 Solution

Accepted Solutions
nguyenviethung
New Contributor III

Re: How to calculate AVERAGE 4 rolling period sales

‌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.

5 Replies
sureshqv
Esteemed Contributor III

Re: How to calculate AVERAGE 4 rolling period sales

isingh30
Contributor III

Re: How to calculate AVERAGE 4 rolling period sales

May be this -

Q.PNG

Re: How to calculate AVERAGE 4 rolling period sales

Hi,

try below expression in straight table

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

Regards,

Regards,
Prashant Sangle
nguyenviethung
New Contributor III

Re: How to calculate AVERAGE 4 rolling period sales

‌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
New Contributor III

Re: How to calculate AVERAGE 4 rolling period sales

‌thanks bro; but i wanted a different thing