Hi all,
I'm trying to make a line chart that shows a running percentage of capacity sold for each zone in a facility. Each zone has a different number of seats, so it has to be percentage sold as opposed to just a running sum to properly show which is filling first. Ideally I think I also want this averaged across the perfs in a production season, but I haven't gotten that far yet.
The formula I've landed on doesn't seem quite right:
( (RSUM( ( [Total Ticket Paid/Reserved Count] , [Zone Group] ) / ( [Total Seat Count] , [Zone Group] ))) )
I have a separate value added for each zone and filtered each down.
In dashboard filters I have season type / season / production season and I've selected only one. Also perf status not cancelled, constituent ID not -999999.
I have days in order date on the X axis. I (theoretically) have percentage sold on the Y axis, though it goes above 100% so I don't think this is working properly.
I have also tried
And those didn't seem right either. Any ideas?
I might be under-thinking this, but would it make sense to use the RSUM function for this? If the sold % will trickled upward overtime by default (assuming large refunds don't happen), would it work to just have the (total sold) / (total seat count), with the x-axis being Order Date?I'm wondering if the RSUM-ing of the % is what's giving you weird numbers.
Maybe check the placement on your parenthesis? You might be RSUM-ing the entire formula rather than just the numerator, in which case you are actually adding up the total percentages each week. (And maybe that's how it got to 4,000%.)
( (RSUM( ( [Total Ticket Paid/Reserved Count] , [Zone Group] ) )) ) / ( [Total Seat Count] , [Zone Group] )
(RSUM( [Total Ticket Count] ))/ [Total Seat Count]
Hi Gabrielle,
Adding on to what's already been offered... remove the Constituent ID not -999999 filter. This is a technique for removing unsold seats from the scope of the widget.
I suggest removing Zone Group from the formulas altogether and instead put that in the Break by panel.
Then you can be left with something like:
RSUM ( [Total Ticket Paid/Reserved Count] ) / ( [Total Seat Count] , ALL( [Days in Order Date] ) )
Ah, these changes did the trick!
I think I understand what was happening now. Thank you all!
Would you be willing to share this dashboard Gabrielle Frasco?
Hi Jen, sorry for the delay. I'm uploading my dashboard here, but I'll note that we realized this isn't the best solution for what we were looking for. Hopefully it can help you!
Our issue is that there are breaks in the line chart for days or weeks that have no data, which makes it a little hard to read with all zones visible. The usual Tess Analytics trick for connecting the lines (this thread) makes my charts look inaccurate. For example, the charts below of the same show without (left) and with (right) the connecting code snippet, tell different stories. In the left chart without the snippet, the sales for orch zone G jump at the same time as orch zone B in week 44, but there isn't any data in week 43 to help create a diagonal jump/line. In the right chart with the connector snippet, it connects orch zone B from week 42 to 44, making it look like sales in that zone took off in week 43, before zone G.
We are trying to assess shifting demand in our zones as compared to previous seasons, so this false impression is an issue. This may still be helpful to track currently on sale shows as they progress.
I have a note on my To Do list to try recreating this widget with some similar-ish zones bucketed together in the values field (instead of having zones in the break by field) in hopes that they'll then have data in each week and I won't have to worry about line breaks, but haven't gotten to it yet. SalesVelocityCurves-ofZoneCapacitySold(indevelopment).dash
Thanks Gabrielle Frasco It is a great starting point for me.
Cross-linking
Will add it to the analytic Coffee! wiki