Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Average of the last 8 days

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,
averagedaily.PNG

Labels (1)
23 Replies
Highlighted
Creator II
Creator II

yes

Highlighted

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;

image.png

View solution in original post

Highlighted
Creator II
Creator II

Thank you so much 🙂
Highlighted
Creator II
Creator II

Hi ,

 

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

 

thanks

Highlighted

Why don't you test it out 🙂

Highlighted
Creator II
Creator II

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

Difficult to say unless you share a sample

Highlighted
Creator II
Creator II

I have data like this.

same logic i want to apply

can you please help?

 

 

Highlighted

and what is the expected output?

Highlighted
Creator II
Creator II

Hi,

 

Please find attached file with Output.

 

Thanks