Solved: Average of the last 8 days - Qlik Community

poojashribanger · ‎2019-01-17

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,

sunny_talwar · ‎2019-01-17

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;

View solution in original post

sunny_talwar · ‎2019-01-17

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.

poojashribanger · ‎2019-01-17

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

poojashribanger · ‎2019-01-17

Please find attached Excel

sunny_talwar · ‎2019-01-17

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

sunny_talwar · ‎2019-01-17

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?

poojashribanger · ‎2019-01-17

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

sunny_talwar · ‎2019-01-17

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?

poojashribanger · ‎2019-01-17

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

sunny_talwar · ‎2019-01-17

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

Average of the last 8 days

Averag of weekdays