v15 Tessitura Analytics question

Hi Magaret,

Thanks for posting these questions. Starting with the performance budget question and then move to the production season sort value... Performance Budget is stored in Analytics on each performance, as it is in Tessitura, so to perform any math on that value, such as summing it by production season, requires "multi-pass aggregation" in the value formula. What that looks like is:

• [AGG] ( [Group By] , [AGG] ( [Value] ) )

Where [Value] is a numeric value (in this case the budget amount) that we want to aggregate [AGG] in some way by another [Group By] field before aggregating that grouped by value once again. In the case of performance budgets by production season, that might look like this:

• SUM ( [Performance ID] , MAX ( [Budget Amount] ) )

Here I'm taking the maximum budget amount within each performance ID. Since the value for budget amount will be the same for all rows within a performance ID, it will return the correct value for each performance, but only once, rather than once per seat within the performance. It then sums those distinct values. When this formula is used as a Value in conjunction with a Row/Category field of Production Season, it will roll up to the sum of the budgets for the performances within that production season.

The sort value answer is a little more complicated. There is no single, native value for production season that sorts them in time, but we 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] ) ) )

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 the [Production Season] field is the only dimensional field on rows/categories in the widget.

To extend this formula to sort past production seasons after all future ones, let's assume that we want production seasons that still have future performances sorted first, by their first performance dates, and that any remaining productions seasons sort after that, also by their first performance dates. That might look like this:

IF( MAX( DDiff( [Days in Performance Date], NOW( [Days in Performance Date] ) ) ) < 0, --if the last performance in the production season is in the past
   ( -1 * MIN( DDiff( [Days in Performance Date], NOW( [Days in Performance Date] ) ) ) ),  --then sort on the number of days since the first performance in the production season, but flipping the sign to a positive number
   MIN( DDiff( [Days in Performance Date], NOW( [Days in Performance Date] ) ) ) )  --else sort by the number of says since the first performance in the production season, retaining a negative number

Here's a sample dashboard (ProductionSeasonSortandBudget.dash) that you can import into your Analytics. Save this file to a place where you can access it from Analytics. At the top of the dashboard navigation pane, in the same Dashboards Options (+) menu where you select New Dashboard, select instead Import Dashboard. Browse to and select the file. It was import and then fail to render. At the top of the dashboard, just to the right of the dashboard title, you'll see the "Seats and Tickets" data source. Click that and select change data source. Then select your own Seats and Tickets cube from the list. It should then render successfully and you should be able to edit the widget to explore and make use of the formulas.

Best,
Chris

I'm having trouble getting the budget to show accurately on a line graph. I have a line graph that shows the running total of sales each day and I want to see it's progress towards the budget. It works fine when a show has a single performance, but does wacky things when there are mutliple peformances. Below is an example of the line chart. The budget for this performance is $1.01M, but the budget line is showing $744,000 at the end of the line. Ideally I'd like to show a "daily goal" that leads up to the final budget, but would be fine with just a straight line of the overall budget throughout. Any thoughts?

The concept you need is Multi-pass Aggregation.

What is a Multi-pass Aggregation Formula?

https://www.tessituranetwork.com/en/Items/Videos/Training/Analytics/taffy-october-2019

...starting at 9 minutes 45 seconds

Formula for the Secondary Value (an example of multi-pass aggregation):

SUM([Campaign ID]), MAX([Campaign Goal Amount]))-[Total Amount]