Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
poojashribanger
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
poojashribanger
Creator II
Creator II
Author

yes

sunny_talwar

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

poojashribanger
Creator II
Creator II
Author

Thank you so much 🙂
poojashribanger
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

I have data like this.

same logic i want to apply

can you please help?

 

 

sunny_talwar

and what is the expected output?

poojashribanger
Creator II
Creator II
Author

Hi,

 

Please find attached file with Output.

 

Thanks