Glad I found this group from the conference. I am trying to answer what I think should be a simple question in Analytics: How are my current sales for a performance and how does that compare to the performance budget that we entered in the performance setup screen in Tessitura?
There is an option in Analytics to pull "Budget Amount", but the usage only appears to show the correct amount when used in a pivot table and only when placed in a row. If I put it in the value section, the amount is not correct. Does anyone know why [Budget Amount] when placed in the rows section shows the correct amount, but behaves differently and displays incorrect amounts when its placed in the values section in the pivot table or in anything else?
Howard Levine
My organization does not have a budget amount in the database for each performance. So if anyone has really sorted this please jump in here.
Does the wrong number you are seeing look like the number of tickets or seats * the budget amount?
If so you might try the Maximum of budget amount rather than SUM.
I'm going to poke at this for a few more minutes.
David Judd shared with me a BRVO -- Festival Dashboard. He has a budget widget where he calculates budget as
```sum(MAX([Price Layer],[Total Budget Amount]))```
He has also done a number of things that look very RMA like.
Sorry, that's all I've got at the moment.
I also recently noticed this problem too. I have a six-show run of Barber of Seville coming back with a billion-dollar goal!
Amy I took this problem to the 10 minute solution center at the Conference and met with Peter Nelson. He said there is a bug with this and was going to send me a formula that would fix it. I have not heard from him yet. I am experiencing the same problem of multiplying when I include performance dates as a row. I have budget amount as a value and if I turn off the date row the budgets are correct. This is all I have for the formula [Total Budget Amount].
Hey all. I worked with Chris Wallingford on this at conference. Here is the skinny. The join to budget is a 1 to many join in 15.1 with performance being the many part somehow (I don't have direct access to my data, so I'm not positive on that join). So, the formula you need is:
sum([Performance ID],MAX([Budget Amount]))
Note: the same thing happens in Campaigns, but you can see the correct formula in the Tessitura supplied dashboards.
The explanation of the formula is as follows: Sum up the max of the budget amount for performances grouped by performance ID. The "group by" is done first in the Sysense formulas, unlike SQL which is at the end of the select statement. So, group by performance ID, get the max of the Budget amount from that group, and add it all up.
Worked for me. Hopefully, this will work for you guys too.
David - thank you for the formula, I thought that was exactly what I was looking for. But unfortunately it is still not working for me. The budget is correct in my pivot unless I add in Performance Date as a row. When I turn off the performance date row my pivot is grouped by production season and the budget is correct. When I add the performance date row the pivot then expands to show each individual performance date and then the budget balloons into over 55 million for each performance. I'm going to send in a support ticket on this.
It looks like David's solution does not work in this particular usage (thanks for the suggestions though), but I did manage to find a workaround. I selected the field [Budget Amount] and clicked the "more" option on the right when selecting this field and added "median" which then changed the field to [Median of Budget Amount]. That seems to limit the amount to just what is entered as the budget within that particular performance in Tessitura. I hope that helps everyone.
Howard,
Excellent! We are getting closer. That definitely fixed the Budget when I add the performance date, but now when I turn off performance date and it is grouped by production season I get only one of the performance budgets rather than the sum of all the budgets in the production season.
Oh bummer, the Grand Total for Budget is also not calculating correctly when I add in the "median" it is showing a small positive amount and should be a large negative amount at this point in our season. It's so close. I can make it work by using two widgets, one showing grouped by Production Season and the second by performance. If I can figure out the Grand Total issue it will be perfect.
Can't replicate this as we don't repeat performances, but it is a little weird that Performance ID isn't a unique enough grouping. Could you try grouping by performance date instead, still grabbing the Max budget amount for the group?
Terry Stevens, David Judd, Howard Levine
So, I've been looking at the documentation for the formula editor. I've not figured this out.
I've come onto the idea of the <Group by Field> and the <aggregation> that is part of the formula syntax.
This is sort of interesting most SUM commands takes a single value the range or field to sum.
Terry Stevens when you change the grouping of your widget, I'm wondering if somehow we have to change the <group by field> in your formula?
If that works, then we might figure out if there is a way to automate this so you don't have to manually change which column we group by with a Case or IF function and a Dictionary filed like a Cube Column.
Or if we somehow have to re-scope our formula with the "All" function and then filter back down to the correct group by.
Not an answer. Just my $0.02. If I get more time to work on this I may give some of this a try.
Hi Friends,
The "multi-pass aggregation" formula that David shared is correct, and if it's not yielding correct results for you, I appreciate you opening a support ticket. Here's an example showing Production Seasons, sub-totalling their performance budgets. Within each production season is the Performance Date (Days), and the distinct Budget amounts as descriptive values (on Rows). The Values that follow are the multi-pass aggregation formula for Total Budget Amount, and a count of performances (within that Production Season, on the same date, that share that distinct Budget Amount).
Taking the first production season in the results as an example, it's total budget amount is $82k across 5 performances with distinct budgets of $14k and 20k. The 2 performances on 23-Dec have different budget amounts and roll up to $34k. The 2 performances on 24-Dec have the same budget amount and roll up to $28k
The use of MAX vs MIN, AVG, or MEDIAN for the inner aggregation of the formula should not matter in the results. All of those should, within the scope of a single Performance ID, pull the same value. I however think of MAX and MIN as being the more performant choice, not involving as much calculation as AVG or MEDIAN.
Finally, multi-pass aggregation can be used on any Value formula that references an Amount that is less granular than the data set's grain of detail. For example in the Contribution Income pre-built dashboard, scroll to the Campaign Progress widget, and find a sum of Campaign Goal Amount, where multi-pass aggregation groups the goal amounts by Campaign ID prior to summing them.
Thanks so much!Chris
Hi Chris,
I do have a ticket in on this. Here is what I am getting for the Grand Total. The two Budget columns are the only ones not giving correct results. I have included a spreadsheet of what the totals should be for those two columns. I'm puzzled as to why the other columns are correct and the budget columns are not.
Tom, I'm not sure how you got to the screen you are showing and to re-scope the formula as you suggest. I am puzzled why it is only the Budget columns which are giving me trouble. I'd be willing to share my screen with you anytime if you want to take a look at it.