Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Average of the last 8 days

Hi All,

I want to calculate average of last 8 mondays or tue so on and leaving current monday sum.same for all weekdays

and then replace that average with all the mondays or tue or fri so on in a table

Please help

Thanks,
averagedaily.PNG

Labels (1)
1 Solution

Accepted Solutions
Highlighted

Try this

Table:
LOAD
    EVENT_DT,
    "weekday",
    Val,
	 Ceil(AutoNumber(RecNo(), weekday)/9) as SerialNumber
FROM [lib://Lib/dummy data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join (Table)
LOAD DISTINCT EVENT_DT as AsOf_EVENT_DT,
	 Date(EVENT_DT - ((IterNo())*7)) as EVENT_DT
Resident Table
While IterNo() <= 8;

Left Join (Table)
LOAD weekday,
	 SerialNumber,
     FirstSortedValue(Val, -EVENT_DT) as AvgVal
Group By weekday, SerialNumber;
LOAD *,
	 Ceil(AutoNumber(RecNo(), weekday)/9) as SerialNumber;
LOAD AsOf_EVENT_DT as EVENT_DT,
	 "weekday",
     Avg(Val) as Val
Resident Table
Where Len(Trim(AsOf_EVENT_DT)) > 0
Group By AsOf_EVENT_DT, "weekday";

DROP Fields AsOf_EVENT_DT, SerialNumber;

image.png

View solution in original post

23 Replies
Highlighted

Looking to do this in the script or front end? Also, would you be able to attach your Excel file so that we can load the data instead of typing it all up.

Creator II
Creator II

sure , will attach excel file.
anywhere it is fine as long as it gives me desired output.
Thanks
Highlighted
Creator II
Creator II

Please find attached Excel

Highlighted

One more thing... you started with Dec 14th instead of Dec 21st to calculate your average... why is that so?

Highlighted

Also, the values associated with each date seem to be different compared to what you have in your screenshot... would you be able to provide the expected output based on the sample you have provided?

Highlighted
Creator II
Creator II

please find attached excel with average column which i want to calculate

Highlighted

For all dates from 31st Oct 2018 till 26th Dec 2018, you have average which is getting calculated based on Val from 31st Oct 2018 till 19th Dec 2018., but you also have less than 8 week average calculated for Oct 3rd till Oct 24th?

Also, what happens as we get another week? The average value just shifts? Now the new average is based on 7th Nov 2018 till 2nd Jan 2019 based on Val between 7th Nov 2018 till 26th Dec 2018?

Highlighted
Creator II
Creator II

if my max(date) is nay week eg monday then ineed average of previous 8 mondays exclusing current monday ,same goes for all weekdays.
for eg if i dont't have 8 mondays available then whatever mondays are available excluding current should be averaged ,be it 2 mondays or 3
Highlighted

and this keeps on getting changed as and when you get new data?