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
Chris you are amazing! In one post you fixed two problems I was dealing with. This one is golden! Can I confirm there is no way then to add other rows to this? We would love to include the Season Type and the MAX performance date in the production season.
Hi Terry,
So long as the production is the first column, I think it should work to add others.