Average of the last 8 days

[poojashribanger]

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,

[poojashribanger]

yes

[sunny_talwar · Accepted solution]

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;

[poojashribanger]

Thank you so much 🙂

[poojashribanger]

Hi ,

Just 1 question will this logic will work if i want to add one more column in my main table.

thanks

[sunny_talwar]

Why don't you test it out 🙂

[poojashribanger]

i did test it 🙂
but because of firstsortedvalue function it doesn't seems like it will work.

[sunny_talwar]

Difficult to say unless you share a sample

[poojashribanger]

I have data like this.

same logic i want to apply

can you please help?

[sunny_talwar]

and what is the expected output?

[poojashribanger]

Hi,

Please find attached file with Output.

Thanks