I am trying to replicate a report we put together in Excel weekly that tracks a running sum of single ticket sales by production season against 3 different groupings - average sales, low (turtles) and high (ponies). All of the data for the comparators was pulled out of T-Stats and we pull the weekly data for performances within the next 8 weeks out of T-Stats, too. We then hand enter that info into excel and convert that pivot table into a line chart. Here is a screen shot of the pivot table and line chart from Excel:
It would be wonderful if I could put together a widget in Analytics to track upcoming performances in this same way but I am getting stuck because I can't figure out how to create custom groupings of performances to track ponies, average and turtles separately (and averages isn't technically the average, I toss out a few outliers) nor how to track against each upcoming production season separately. In the line chart in Analytics you can only break by one unique item. Though you can filter that item I can choose one production season or a grouping of production seasons but not several groupings of production seasons.
Even in pivot table form, if you filter prod seasons anywhere it affects that measure everywhere, it isn't possible to add two instances of prod season in columns for example and filter them separately.
We are RMA users and sales curves are easy enough to use there to track individual production seasons but even in RMA you can track against one forecast (average for example) but not several forecasts to split the 3 comparator lines.
Is there some work around I'm missing here?
Thanks,
Amy
Amy,
I’m not sure exactly what you are trying to do but if you want a different column for each production season you select try using the Text function in the Column filter.
Thanks, Terry! I didn't know that was an option, that is helpful. Now I just need to figure out how to create those comparator columns, too that contain a mix of prod seasons.
Check out this latest video. It think it might help you.
https://www.tessituranetwork.com/Items/Videos/Webinars/2019/Tessitura_Analytics_Skills
Here's the topics discussed:
Thanks, Terry and Cole. That gets me closer and would work fine if it was just a pivot table but when I move the pivot into a line chart the buckets aren't working quite right.
It either puts the graphs side by side in columns:
And you can't break by a formula so if I break by prod season it unselects one of my buckets (or more, down to just one bucket).
Hi Amy. It's the 2x RSUMs that always do it for me. I went to the Break by Custom Field for that reason. I'll give it a try over the weekend and see if I can come up with something. (Pesky shows are getting in the way).
I think that RSUMs are severely limited in a line graph. I'd love to have an RSUM hockey stick graph (contributions, ticket sales etc) with a Goal amount displayed. The RSUM always breaks the formula. Same with aggregations.
Hi Amy,
Would you open a support ticket on this, please? I'd expect your first example with the 2 values to work, and am surprised to see the 2 sets of weeks in the output. Here are 4 RSUM values in the same chart. The first two being groupings of Subs vs Singles, and the second grouping based on sub-selections of performances within the selected filter of performances.
Best,Chris
Heath Wilder Neil Cole Terry Stevens - thanks to help from Chris Wallingford I have solved this dilemma!
So it turns out that my running sums were displaying in two sections of the same widget because I had the formula incorrect. I had a parens before RSUM and at the end of the formula instead of RSUM and then the parens. Once that was set I could see the different buckets in unique lines on the sales curve but my next challenge was trying to limit the way the data displayed so I was only looking at the last 8 weeks of sales while still accumulating sales from the entire sales cycle and I also needed to loook at the average and not total sales for each bucket. I was able to figure out the average by dividing the total sales for prod seasons in that bucket by the number of unique perf codes and then Chris helped me write a formula to combine that formula needed to display the running sum all of the sales within that bucket but only display the last 8 weeks. In the end, that formula looked like this:
RSUM( CASE WHEN MAX( [Order Weeks Prior to Performance] ) = -8 THEN ( [Total Ticket Paid Count] , [Production Season1] , [Order Weeks Prior to Performance1] ) / ([# of unique Code] , [Production Season2] ) ELSE ( [Total Ticket Paid Count] , [Production Season2] ) / ([# of unique Code] , [Production Season3] ) END )
Where order weeks prior to performance1 was filtered to look at equal or smaller than -8 and every occurance of production season is filtered by the perfs in that bucket. The whole widget is filtered to look only at single ticket price type categories. In the end, it looks like this, with upcoming prod seasons tracked individually against the 3 comparator buckets with the line flattening out for upcoming concerts because there are no sales beyond those weeks.
Excellent Amy Catanzaro
Amy Catanzaro
Would you be willing to share this widget in a dashboard by itself or the entire dashboard containing this widget? You would do this by exporting the dashboard into a .dash file, and then upload the .dash file here? I would love to apply your wonderful widget to our data and see what we can learn.
Thanks for considering this.
1325.1920Singles.dash
Here you go,