Good morning,
Our guided tours have been suspended since we shut down in March and we don't plan on bringing them back in the near future. But, as we plan for the day that we do, our visitor services manager would like to see a chart showing the average attendance by zone for the last year or two by day of the week. I'm guessing I need to use bucketing to do this, but I was curious if anyone else as put something like this together yet. (I"ll have to go back and rewatch that video!). Here's our setup -- we offer our tours 4 times a day Tues-Sat and 3 times on Sun. I am looking for a bar chart that shows the average number of tickets sold on each day of the week in each of those 4 time slots for a specified time period (not each individual day).
Any advice would be most appreciated.
Thanks!Anne RobichauxThe Historic New Orleans Collection
(I think I'm onto something, but I gotta talk things through. Here goes!)
Average Attendance by Zone would be something like [ (total # of tickets , for a specific zone) divided by (total number of zones , for a specific zone) ]. I think that makes sense. So if there were 2 total instances of "zone A", and 100 total tickets sold in "Zone A", the average would be 50.
I think I've arrived at a workable solution. I'll use our 4D theater (which runs every 30 minutes so far)
I tested this out and it seems to work. You could then filter the whole widget by a certain production season(s), and/or certain days of the week. I used a Pivot (they're my favorite), but a bar chart would work as well!I don't think you'll be able to get around building a separate bucketing arrangement for each zone, however. In your case, there's only a few zones, so that's not too bad. For our theater, there'd be dozens. The things we do for coherent data!
Let me know if that helps. This was a fun one
Thanks, Nathanael Pearson! I'm going to give it a go and will let you know how it turns out.
I think I have it set up the way you suggest, but something is really off. My number of zones per day should be roughly equal and they are way off. Plus, I'm seeing counts on days when we don't even offer tours at this time (we're closed Mondays, and our 10 am time slot isn't available on Sundays). Can I send you my dashboard to take a look at whenever you have a chance?
Absolutely! npearson@mysticaquarium.org
Off the top of my head, you might be inadvertently using the DoW from order date rather than performance date? That would explain the unexpected days.
Thank you! That is definitely part of the problem.
Actually, that might be all of it! Thank you!
I think the solution posted below has solved my issue. Thanks for your help!
I have a related oddity. In reviewing my numbers, I see that my formula for selecting only the number of zones with a specific title doesn't seem to be working for me- the number of zones seems very high.
We have 45 given zones for 4D theater each day. When I select for a single performance, and have the formula (# of Zone ID, Zone = 10:30am Small foot), it returns 44 as a number. Shouldn't it return '1' ? When I select for two performances, the number is now 88
The third formula is functioning as expected, but that second portion- finding the number of zones in a given period- seems off. I can divide that second formula by 44, and arrive at the right number, but I'd like to be able to do it without bringing in a number like that.
Any ideas?
The way I read that second formula is that it is giving you the total number of instances of that zone in the production season, so basically the number of perfs that include that zone.
I had thought it would do something similar, but that 44 number doesn't mean anything- it's not # of days per month, it's not # of zones per day, or zones per month. it wouldn't be # of zones in the season, as we have much more than 44 days with that zone in it.
I have a workaround, but I'm confused as to why it returns 44.
Do you mind sharing your workaround? I'm now worried that my numbers are off.
In my case, the number seems to always be (# of perfs x 44)- when I look at one perf, it says 44, two perfs = 88, and so on. Given that the ratio seems static, I modiy the second formula to this:
( [# of Zone ID] , [Zone]) / 44. As I move the number of perfs upward, the ratio seems to hold. I don't know why it is that way, but it is!
I copy this into the third formula, to arrive at .
([Total Ticket Count] , [Zone1]) / ( ( [# of Zone ID] , [Zone1]) / 44)
I realized that something is definitely off in mine too, but not by an even number. I only have one tour in each widget and there is only one per per day (with 3 or 4 zones), but for one FY I'm getting more than 100 to more than 150 for the total number of zones on each day. Now, we did have to change our setup partway through the year so I would it expect it to be more than 52, but if one FY = 1 calendar year, shouldn't there only be roughly 52 for the total number of zones day?
Ok, I think I found a work around. I changed that formula to one that counts the number of unique Perf Ids and then put that into the next formula. Each perf only has one of each zone, so it should give the info we are looking for.