Qlik Community

Qlik Sense App Development

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

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th 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

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