Aggregate attendance by zone

(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)

• The first value would be Total Number of Tickets Sold, for 1030 Small Foot Zone:
  • 
    •  where you'd filter the 'Zone' one by selecting only the '10:30 Small Foot' zone.
• The second value would be Number of 10:30 Small Foot Zones:
  •  
    • 
• The third one (the one you were really looking for) would be (Number of ticket sold into 10:30 small foot, divided by Number of 10:30 small foot zones)
  • 
    • where you'd just copy in the formulas listed above.

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

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? 

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.

That makes sense to me. I'm not sure why asking "How many zones are there, where zone = X" is returning such high numbers.

I'm trying to think through why how to refine the question for Analytics. The question is, essentially, "How many times has X zone been offered, for a given time frame". For Mystic, given that there is only ever one zone per day, I could modify things to be (# of Perf ID) as a substitute for (# of 10:30 Smallfoot Zones), which would avoid having to divide by 44.

In your case, I'm not sure how to break that up without using explicit numbers.

To make matters even weirder, my original second formula (# of Zone ID , Zone = 10:30 Smallfoot) is now (as of 12.1.20) returning a different number! Now, when I select just one performance (11/15/20 in this case), it returns 20 as the number of zones. When I do two days (11/15 and 11/16), it returns '40'. Again, that number doesn't mean anything- total zones for the seat map were 45. Total zones available on that day were 17. 20 seems a random number.

I'm going to submit a TASK to see if I can sort this out.

Please let me know what you find out. In my case, I think the issue is solved but I'd love to know why it isn't working the way you originally set it up since it seems like that should work and would be the most accurate.

How have you resolved the issue on your end?

I'll keep you posted! I just submitted a TASK with the .dash file attached.

In our case, TASK recommended going down the # of Unique Perf ID route, rather than # of Unique Zone IDs. Again, this works because we have each zone running only once per day.

I might be overthinking things. Each zone that you have per day, I assume that each zone is separately labeled, and has a separate ID? In that case, each zone (1, 2, 3, or 4) would be synonymous with the number of performances.

We could do something like this: 

Categories of Zone, to break things out.

Values of Total Ticket Count, and 'Avg Tix per Perf'

Does that work? It's been a few days since I dived into this, I might be missing something.

If you wanted to use bucketing, and have each Value be a separate zone, you could use:

where Zone is filtered by whichever zone you'd like.

In our case, TASK recommended going down the # of Unique Perf ID route, rather than # of Unique Zone IDs. Again, this works because we have each zone running only once per day.

I might be overthinking things. Each zone that you have per day, I assume that each zone is separately labeled, and has a separate ID? In that case, each zone (1, 2, 3, or 4) would be synonymous with the number of performances.

We could do something like this: 

Categories of Zone, to break things out.

Values of Total Ticket Count, and 'Avg Tix per Perf'

Does that work? It's been a few days since I dived into this, I might be missing something.

If you wanted to use bucketing, and have each Value be a separate zone, you could use:

where Zone is filtered by whichever zone you'd like.

I also used Unique Perf ID and it gave me exactly what I needed, number wise. It isn't working as a bar chart for me because our numbers are so low that you can't see the averages (in most cases it is averaging less than one person per zone!). But because the numbers are all small it is easy enough to compare them in a pivot table. Here's what I came up with for each timeslot. Thanks again for all of your help. Our Visitor Services Manager was very pleased with the final result.

Great! You're quite welcome- I'm glad it worked out. Pivot tables are my favorite, usually.