Does this look about right to you?
You can duplicate the base here so that you can look at the formulas and stuff
The idea is to use a "Count" field to get the number of days you're open, use a rollup to sum the total attendance values, and use the "Unique Week Identifier" as the grouping. We can automate this by creating an automation that'll paste the "Unique Week Identifier" value into the linked field too
Are you looking for a single average weekly attendance, or an average daily attendance per week?
If you want an average daily attendance for days open, Adam’s approach of using linked record fields and rollups is the way to go. However, you do not need three fields. You can do it all in one rollup field with the formula AVERAGE(values).
It isn’t clear to me what condition you want for which records to include, but you can include that condition in your rollup field.
You can learn more about rollup fields here.
Does this look about right to you?
You can duplicate the base here so that you can look at the formulas and stuff
The idea is to use a "Count" field to get the number of days you're open, use a rollup to sum the total attendance values, and use the "Unique Week Identifier" as the grouping. We can automate this by creating an automation that'll paste the "Unique Week Identifier" value into the linked field too
Hi Adam!
Thanks for the help, this seems to be on track for what I am looking for! Is there a way to automate the dates that populate as linked records based on the unique week identifier? The entire data set has almost two years worth of data so it would be much easier if I didn't have to manually select the dates that are in a given week. Thank you again!
Hi Adam!
Thanks for the help, this seems to be on track for what I am looking for! Is there a way to automate the dates that populate as linked records based on the unique week identifier? The entire data set has almost two years worth of data so it would be much easier if I didn't have to manually select the dates that are in a given week. Thank you again!
Yeap there is, you can create an automation that'll trigger when the "Unique Week Identifier" field is not empty, and the "Weeks" field is empty, and it'll have an "Update Record" action that'll paste the "Unique Week Identifier" value into the "Weeks" field, and that'll work for all new records
For your existing records, you can just click the header of the field "Unique Week Identifier" (thereby selecting the whole column of data), clicking the header of the field "Weeks", and just pasting and that should just work!
Be sure to use @kuovonne's solution about using `AVERAGE()` too, it's a lot cleaner than mine. Thanks kuovonne!
Are you looking for a single average weekly attendance, or an average daily attendance per week?
If you want an average daily attendance for days open, Adam’s approach of using linked record fields and rollups is the way to go. However, you do not need three fields. You can do it all in one rollup field with the formula AVERAGE(values).
It isn’t clear to me what condition you want for which records to include, but you can include that condition in your rollup field.
You can learn more about rollup fields here.
Hi Kuovone,
So I have done that but I am unsure how to make it so the "where" statement encompasses whenever the unique identifier is the same. For instance the fifth week of 2023 will have the unique week identifier "20235" for each day the attraction is open and I need the average where the week identifier is the same, do you know how I could accomplish that? Thanks so much!
