I have a view where on 1 axis I have displayed Hours (0-23) and on another axis I have displayed Weekdays (Mon-Sunday).
And in the table values are displayed the average count of the last 5 weeks
I calculate this average by simply filtering the last 5 full weeks and creating a calculated field of number of records divided by 5.
But of course this basic method requires me to update the filter every week manually. So I would like tableau to update it for me automatically instead.
What I would like is a dynamic moving average calculation with the following 2 criteria:
1. The first day of the dynamic 5 week average should ALWAYS be a Monday. The last day should always be SUNDAY, meaning the moving week average should only shift forward a week once the current week is complete.
2. The possible issue where the most recent days records are incomplete and taken into the average should be avoided. I would like the average to not be based on today(), but instead max(date) -1.
Sorry if this 2nd point is confusing, let me give an example:
If I refresh the data on Sunday morning, I now have a full week of data allowing the average to be moved forward 1 week. HOWEVER since I refreshed the extract in the morning, more records for Sunday will be generated as the day goes by. So to avoid the issue of an incomplete count of Sunday being included in the average, the average should only be moved forward once it sees that Monday has at least 1 record. This would obviously mean Sunday's data is complete since there is already data on Monday.
Workbook is attached, any help would be appreciated the. I'm not that good with date functions and getting confused trying to figure this out.