Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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;
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.
Please find attached Excel
One more thing... you started with Dec 14th instead of Dec 21st to calculate your average... why is that so?
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?
please find attached excel with average column which i want to calculate
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?
and this keeps on getting changed as and when you get new data?