Avereges of last n Days of the week

[robert_mika]

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.

[sunny_talwar]

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

[robert_mika]

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]

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
Mon	0.8
Tue	0.8
Wed	0.8
Thu	1
Fri	1.2
Sat	1.2
Sun	1

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.