Hi All--
I'm a bit stumped on something I feel should be possible... I'm looking to show a % of total capacity sold in the 30 days prior to the first performance of a production season. I'm looking at similar productions all with different capacities, so we're hoping to see a more relative % of the house sold prior to the first perf.
I have a line chart going with Order Days Prior to Opening going on my X-Axis, and my Break by production is working nicely. My formula is a mess, however.
The only clean version I have is to get a running sum of total tickets sold with 'RSUM(SUM([Total Ticket Count]))', but from there I'm stumped on how to pull capacity in and make the formula work. I tried 'SUM([Total Ticket Count]+[Total Seat Unsold Count])' to no avail.
Does anyone have this nailed down? in plain English, I'd like to get a formula going that achieves a running % sold in the 30 days prior to 1st perf (total tickets sold/total capacity), broken by production season.
Many thanks for any help!
Michael Buffer | Park Avenue Armory
Chris Wallingford (or really any brilliant mind on this forum!) A follow-up question here! The ALL solution for the line chart has been popular, but my team wants to create a more manageable view of this data in a small pivot table, looking at one relative date, such as Order Days Pior To Closing = -89. My hope is to be able to provide a widget where a small range of dates or a single relative date can be viewed using a filter.
What I've discovered is that I can make a BIG unwieldy pivot with every possible order day, but even with the ALL function in my formula:
RSUM ( [Total Ticket Count]) / ( [Total Seat Count] , ALL ( [Order Days Prior to Closing]) )
As soon as I filter down to a single Order Day prior to CLosing, it seems to lose the context of all prior days, defeating the ALL and the RSUM. In the use case, we have two productions on sale being tracked in relative success to prior comparable events. Again, we have a successful line chart thanks to your help, and I've made it into a big pivot, but I can't narrow the pivot down... what am I missing here?
Alternately, is there a way with a breakout line chart to show values only at a certain day? i.e. I could solve this problem by showing values on the existing line chart, but only for a single relative order day. Because it is so long, values are confusing to publish with, but sending PDFs or printing makes the charts less useful for those who are analytics-challenged.
Thanks for any advice as always!
Hi Michael,
You want to show current % capacity sold for two productions, separately, and their current weeks from closing (do they differ?), and third value that's the % capacity sold for historical comparators as of the same week from closing?
Thanks, Chris- I actually want to see them all together, just limited to the % of capacity sold at a specific point in time, in this case, a relative day to closing...in real date terms they all differ in length of run and onsale, so it's a tricky thing to narrow down. Order days prior to close seems to be the most complete way to get an accurate set of values, but unless I look at them all, I lose the data for the formula to crunch.
When Filtered, the hope is it would look like something along these lines:
Where, given the current production under scrutiny's order days prior to close (i.e. -89), we can see where the %capacity sold stood for the other comparative productions on the same relative date.
Our line chart does this really well, but is tricky to read if you are not in analytics directly, hovering over the values- we want to deliver this to executives who prefer a static image or print. Compounding our long view line chart with a super simple one-row pivot would make it easy to get a handle on where we stand at the time of reporting.
I should say that when the filter is applied now, it seems to be calculating the single day of tickets against the full-run capacity. I tried varying the formula like this to get ALL tickets, but it returns an error:
RSUM ( [Total Ticket Count], ALL( [Order Days Prior to Closing])) / ( [Total Seat Count] , ALL ( [Order Days Prior to Closing]) )
It accepts the formula, but errors when querying the cube and trying to render results. I feel like I'm right on the edge of this, just missing something!
Could we consider restructuring this a bit? For example,
I went a little sideways, as I couldn't get 'Division' to work for me, but I moved Production Season to Rows to limit the values:
This rendered what I was hoping to see, and proves out with the other widgets! The MAX formula for Order Days Prior to close allowed me to use the widget filter. Perfection!
I may do some formatting to make the row-by-row '-##' disappear, but it's a small price to pay to get a dashboard in front of execs! Many thanks again- figuring out widgets is a *favorite* part of my day, any chance to learn something new! I'll need to look at Division, but for now, I'm hoping this passes muster!