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
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;
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?
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?