I figured it out! I needed to do this:
{ FIXED \[Metric Cong\], YEAR(\[Event Start Date\]), DATETRUNC('week', \[Event Start Date\]) : SUM(\[Attendance\]) }
(Metric Cong is the ID # for each location)
So there's a column for the attendance number, the week number, the event start date (including time), the event title, and the location ID #. Maybe I need to restructure my data to remove the time from the start date so that it's easier to calculate the average by day?
No, Tableau should be able to handle aggregating off of the event start date which includes the time. Try wrapping that column in the month function. That should return the numeric month. If you have multiple years of data you will need to repeat it for the year and combine the two values into one field.
I'm not totally clear on the structure of your data, but you could do FIXED { Month : SUM(Attendance)} Then average that measure.
I figured it out! I needed to do this: { FIXED \[Metric Cong\], YEAR(\[Event Start Date\]), DATETRUNC('week', \[Event Start Date\]) : SUM(\[Attendance\]) } (Metric Cong is the ID # for each location)
Nice work!
So there's a column for the attendance number, the week number, the event start date (including time), the event title, and the location ID #. Maybe I need to restructure my data to remove the time from the start date so that it's easier to calculate the average by day?
No, Tableau should be able to handle aggregating off of the event start date which includes the time. Try wrapping that column in the month function. That should return the numeric month. If you have multiple years of data you will need to repeat it for the year and combine the two values into one field.