I created a simple dashboard looking at the count of first time buyers with a pivot table count widget and a bar chart widget for the current season and previous three seasons. The widgets default to sort by performance name alphabetically, but I'd prefer the list to be in order by when the performance occurred. This would make it easier to visually look across different seasons for shows that happen at similar times of the year.
For example, this year lists Beauty and the Beast (happening in Feb.) prior to Nutcracker when (happening in Dec.). Using the month as a second category starts to help, unless there is a case where a show occurs across two months, and then I end up with two rows for the same production.
I brought this up during Analytic Coffee today and others have had the same question - so we're posting to the forum for any advice!
My dashboard's .dash file is attached if anyone wants to use it as a base to try options.
PBTFirstTimeBuyers.dash
Analytics v 15.x doesn't currently have a sort value for Production Season other than the name of the Production Season itself. However, you can create a value formula that will produce one based on the earliest performance date in each production season:
MIN( DDiff( [Days in Performance Date], NOW( [Days in Performance Date] ) ) ) --positive numbers on current/future productions, sort ascending
OR
-1 * MIN( DDiff( [Days in Performance Date], NOW( [Days in Performance Date] ) ) ) --positive numbers on past productions, sort descending
This takes the minimum difference in the count of days between the date of the performances in the production season and today. This will be a negative number for past performances and a positive number for future performances. In essence, this sorts based on the first performance date in each production season. To sort by the last performance date in each production season, the formula can be modified to use MAX instead of MIN:
MAX( DDiff( [Days in Performance Date], NOW( [Days in Performance Date] ) ) )
Using these values assumes that production season is the only dimensional field on rows/categories in the widget.
I happen to have a sample: ProductionSeasonSortandBudget.dash
Then for a column chart to use this sort value, requires that you not be using a Break By in your chart. Add, for example, the sort value on your chart. Put it into the first position among the values. Sort by this value, and if desired you can rename it to something less visible (like simply a period, "."). Set the color of the value to white. Then click on the hamburger for the value, and change the Series Type to Line. This will render the white line invisible behind the rest of the chart elements, but still enforce the sort. Here's an example with Zone rank and there are examples of this in the pre-built dashboards.
Best,Chris
Chris Wallingford Director, Business Intelligence Tessitura Network office: +1 888.643.5778 x553 chris.wallingford@tessituranetwork.com
Thank you so much, Chris!
I'm getting close to having this work - is there something different that affects the first performance? The formula worked perfectly for two of the past three seasons and the current season, but I'm running into issues with our 2018 season. In the example below, The Great Gatsby should come after the Nutcracker productions as it took place in February. If I switch to look at production season, the numbers do change and match correctly:Here's a look at one where it worked with first performance - in the 2018 year the formula ordered productions as expected:I'm using this version of the formula so that when I turn things into a bar chart the numbers are all positive in past years:I was using price type category as the break by in my bar charts, so I'll just need to figure out a different way to represent those numbers.Thank you!Christine
Hi Christine,
I missed the part where you're using First Performance Name... That's... trickier. The formula is intended for sorting Production Season. It's using the performance associated with the tickets in your widget, whereas the First Performance Name is just a Constituent level flag. I'm not sure what it would do if within the collection of constituents for whom The Great Gatsby was their first time purchase also purchased tickets associated with other productions in the selected season. What is the MIN difference between NOW() and all 2019 BT performances purchased by constituents for whom Great Gatsby was their first?
You could explore changing the logic of the TX_ANALYTICS_DIVISION_ELEMENT for First Performance Name to prepend a sort code.
Beyond the sorting, you could probably exploring creating a filtered value per price type category to get those back.
Chris
This has just changed the way I use Analytics. This is a goldmine. Exactly what I needed.
Thanks, Chris. Someone in our consortium took a look into first performance date field and now everything is aligning correctly! Thanks so much for your help with this question!