Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
robert_mika
Master III
Master III

Avereges of last n Days of the week

How to calculated averages of the last 5 weeks  of  days of the week using set analysis?

If my Dimension is Day of Week:

M

T

W

Th

Fr

Sat

Sun

I need to take last 5 previous weeks ( not included current) and calculated the averages.

I need the AVG of COUNT not SUM.

3 Replies
sunny_talwar

Robert would you be able to provide a sample of what you are trying to achieve?

robert_mika
Master III
Master III
Author

Hi Sunny,

Thanks for replay

Please see attached

I need to count the ID per each day and then take the averages per each day of the week.

I assume I need to use aggr,

I was playing with Stephan swuehl suggestion from this post:

Show average value for weekday

but It does not work for me .

I need count  the values first

and In real situation some of the dates are missing:

DayofWeek, Date or even ID

so the Only function may struggle here.

Thanks.

effinty2112
Master
Master

Hi Robert,

Does this table give you what you're looking for?

The expression is:

Sum(Aggr(Count({$<Date = {"<$(=WeekStart(max(Date)))>=$(=WeekStart(max(Date)-5*7))"}>}ID),Date))/5

DayofWeek Avg / Day
Mon0.8
Tue0.8
Wed0.8
Thu1
Fri1.2
Sat1.2
Sun1

The fact that you're looking back over a fixed period of five weeks helps keep things simple. I'm guessing that a day missing from that data counts as a day with zero occurrences of the event we're counting. In other words for all weekdays the average is obtained by taking the sum and dividing by five, not dividing by the number of days featured in the data.