Essentially, I'm looking to compare one perf's running sum of ticket count vs the others in that production season, in the same chart, as it pertains. Or: how is this perf's sales doing over time, compared to the rest of the season?
I can get the total ticket count for a production season, in a running sum, with 'Order Days Prior to Perf' as the X-Axis. I can get the total ticket count for a performance, in the same format. But I can't seem to get both on the same chart. It brings up the Yellow Exclamation Mark of Doom when I do so:
I can do two sums, but not two rsums. I can't do one sum and one RSUM either. The RSUM isn't playing nice with anything, even another RSUM.
Within a widget filtered by Production Season, the formulas I'm using are: RSUM ( [Total Ticket Count] ) for the whole season's ticket count.
( RSUM ( [Total Ticket Count]) , [Days in Date] ) is what I have for the bucketed result, filtered by a single performance. Again- both work, but they don't work simultaneously
Is there a better way to do this? Having a nice visual for comparison would be powerful.
Here are a couple of ideas:
1. Add TRUE to your RSUM, so that it accumulate the sum continuously.
Something like: (RSUM ([Total Ticket Count], TRUE), [Days in Date])
Example:
2. Try adding Performance to the Break By part of the graph.
This graph has two RSUMS with Fiscal Year in the Break By part.
Hey Pal,this is quick and dirty but this sort of thing? This is the RSUM of the Ticket Paid Count broken by Performances in the 1st week
2626.RSUMPerfs.dash
Now that I think about it a little more, I think that the TRUE part will only help when there is more than one dimension in the X axis. My graph has Day of Month and Calendar Sort Month. Without the TRUE in the formula, the RSUM would drop back down to zero for each month.
Use Heath's .dash.
Thank you both for your help! I'm still running into some errors.I can get one line that shows the RSUM of one performance. I can get a line that shows the RSUM of all performances in that prod season. I can get a line that shows the average ticket count per performance.
I can't get, on one graph, one line for the RSUM of one performance, AND a second line for the average ticket count of all performances.
In other words- I want one line that I can tune to a specific date, and one line that shows the running ticket count for a month (or year, or all other performances to date). Does that make sense?Running ticket count (Jan 30th) vs running ticket count (January as a whole).
I can get two sums, no problem.
I can get one (RSUM of the total ticket count) + (sum of one day's performance).
As soon as I make the single day view an RSUM, it won't do both, even if I switch the total ticket count back to a simple sum.
It seems the RSUM, coupled with the specific date focus, is causing it trouble. Analytics can do the RSUM for a specific date fine by itself, but it won't display that plus anything else. It will not display an RSUM of one perf plus any other value - I've tried a dozen different random values.I can use the RSUM, broken by perf Date, and then hover over Jan 30, and use that as a visual comparison to the other perfs. This is good! But I can't seem to refine the visuals, using the RSUM values, like I want to.
Hi Nathanael,
I'm not 100% clear, so I'm latching on to "one line for the RSUM of one performance, AND a second line for the average ticket count of all performances."
The single performance in the dashboard filter is rendered with this:
RSUM( ( [Total Ticket Count] ) )
And the average of all performances within the selected season dashboard filter is this:
RSUM( ( [Total Ticket Count] , ALL([Performance Date and Code]) ) ) / ( [# of unique Performance ID] , ALL([Performance Date and Code]) , ALL([Days in Date]) , ALL([Weeks in Date]) , ALL([Months in Date]) )
The ALL([{whatever} in Date]) are Order Dates meant to override the grouping of the X-axis at whatever grain we might change it to.
The Ticket Count > 0 is just to filter out unsold inventory which have an Order Date of 1900-01-01, but doing so without using the Order Date field. That field is already so heavily in use here on the X-axis and in those ALL functions, this avoids being hindered by the hierarchy of it also being a dashboard filter.
This is grouped by Month in Order Date. To change it, click on the January month to select it and add a dashboard filter. Then edit the widget and change the grain of the Months in Date X-axis to Days.
running_2D00_ticket_2D00_count.dash
Thank you! This is helpful.
To clarify what I was looking for- I was looking for a way to show the running total of sales for a given perf (Jan 30th, as an example), when compared to the other perfs (in a given month, or for the full year, or whatever). In other words - "How well did 1/30/21 do, vs the other perfs (when looking at ticket count)'?I think your dash is what I'm looking for. When I set the filters like this:
It gives me these lines- (I changed the X-axis to 'order days prior to perf')
My interpretation is this: the Orange line is the running total for ticket count, for that 1/30/21 perf, at each of the intervals on the bottom of the graph. The blue line is the RSUM average for ticket count, for all perfs (within the perf month I selected).
As long as I keep the 'Performance Date and Code' constant on the date I want to focus on, I can then change the 'perf month' or 'perf day of week', and the blue line will describe those settings. The orange line will stay fixed on the one per date and code.Are my understandings correct?
Yes, I believe you are correct.
You may have to adjust the Avg formula however, by
This is what I've got now:
Would I need to add in the 'ALL([Order Days Prior to Performance]) to the RSUM half as well?
That looks correct from here. No to adding that ALL to the RSUM portion. Doing so would take the total for all days prior (the total for the whole widget), and rsum it each day prior. So day -36 would start at 1.6k, and -35 would be 3.2k, and -34 4.8k, etc...
Great, thank you!