Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Robert would you be able to provide a sample of what you are trying to achieve?
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.
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.