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,
yes
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;
Hi ,
Just 1 question will this logic will work if i want to add one more column in my main table.
thanks
Why don't you test it out 🙂
Difficult to say unless you share a sample
I have data like this.
same logic i want to apply
can you please help?
and what is the expected output?
Hi,
Please find attached file with Output.
Thanks