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?
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.
Hi Terry,
Your formulas need a correction. I’ll jump on you support ticket.
Thanks!
Hi, Chris-
I've replicated this and swapped production season with performance.
On a few performances, the budget amount is being duplicated. For example, the budget is showing $200,000 for each performance, where it should be $100,000 for each of the two performances, for a total of $200,000. Both performances are on the same day, at different times, but within the same production season. They have the same performance name, so I added in performance ID to test, and am still getting the duplicated amount for both performances.
Is this something I should submit a ticket for, or am I simply overlooking something obvious?
Thanks for your help!
Hi Rachel,
Yes, a ticket would be best at this point, please. Would you also "export" the dashboard you're working from, and attach it to the ticket. Please also include a screenshot of the results you're seeing - once we import the dashboard we won't see it with your data.
Thanks tons!Chris
Will do. Thank you, Chris.
I've been working over in the membership cube. (I know this is TAMATO. However, please bear with me.)
I've tried the sum([Campaign ID], min([Campaign Goal Amount]))
That seems to be doing the right thing for me. However, do I need to go down to some lower aggregation point than [Campaign ID]? If so how do I know where to set this point.
The Campaign Goal Amount is recorded at the grain of the Campaign and no granular. So Campaign ID is an appropriate choice for the group by field here, and is found in the same field collection as the Goal Amount. Were you using a more granular goal amount (eg. Appeal Goal Amount), then you'd select an appropriately more granular group by field (eg. Appeal ID, found in the same field collection as Appeal).
Cheers,Chris
Chris Wallingford We're having this exact same issue with Campaign and Appeal Goal Amounts not totaling correctly. Would you be able to send the correction to the formula you were talking about here? Were using this.. can you help us correct so that it totals correctly?
SUM([Campaign Fiscal Year],[Max Campaign Goal Amount])
Thank you!
Lily
Hi Lily,
Use Campaign ID (select "All Items") in place of Campaign Fiscal Year and you should be golden.
Thanks.
THANK YOU! That's been bugging me for months!!