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?
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.
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.
Terry Stevens
The screen I shared above is a piece of the formula editor screen. Where the whole list of functions are listed and described. I was poking around trying to understand how the functions language works.
See Chris Wallingford notes below as a way to go forward. Getting support on a ticket will be the fastest way to an answer for you.
But, I do invite you to share some results here, so that we as a community can learn from the work you are doing.
Tom, Chris did give me a solution and I will post tomorrow once I import the dashboard. I had some formulas wrong which was what was causing the issue.
Tom - I am still experiencing the same problem even after the fix from Chris. I'm not sure if this is only a problem with our Analytics or not. My ticket is still ongoing on this.
Thank you everyone for all your help and a special kudos to Chris for giving me the final fix. I have attached the final dashboard. This is what we call our Daily Wrap report. It shows ticket sales, budget to go and yesterday's sales.
7776.TST-DAILYWRAP2020.dash
The issue I had was with the formulas. Here is what Chris said:
I’m seeing a formula like this… simple [Total Budget Amount] in your dashboard.
Changing that to SUM([Performance ID] , [Max Budget Amount]) seems to correct the problem in all the various places.
For Remaining Sales Needed, I adjusted it from SUM([Total Ticket Paid Amount]-[Total Budget Amount]) to[Total Ticket Paid Amount] - SUM([Performance ID] , [Max Budget Amount]), replacing the [Total Budget Amount] as above, and removing the initial SUM which is unnecessary. What’s inside the SUM formula is already two aggregated values.
I was having trouble after importing the dashboard Chris corrected because neither he or I realized he only fixed the top widget not the bottom. Once I fixed the formulas in the bottom we were good to go.
Thanks Chris and Terry for figuring this out! I would love to load Terry's sample that she linked to. Is there an easy way to do that?
Here's a link to a file with details:
https://community.tessituranetwork.com/topical_groups/tessitura-analytics-for-fundraisers/m/files/57414
Thanks!
Thanks for sharing!
I'm digging in trying to understand why this is working and how to reproduce such a result.
In particular, I'm looking at the remaining sales need formula. In particular, how did we get Sisense (Tessitura Analytics) to create the formula for remaining sales needed? If I "flatten" the formula the Performance Budget Ammount shown in this formula seems to be our friend SUM([Performance ID] , [Max Budget Amount]). So what is the formula editor doing for us here? And how was this formula originally edited/put in place?
Tom,
I know I was wondering the same thing. We used that formula in the Budget Amount and I am guessing any formula used above can be used in the formulas used under. At least that's what it looks like it is doing to me. I'm sure Chris will chime in and explain.
By the way I had an error in the Dashboard where my Total Tickets were supposed to include Comps and it did not. There two other columns No of Comp Tickets and No of Paid tickets which shows the breakdown of the Total tickets into number of comps and number of paid tickets. The No of Paid Tickets was also incorrect because the formula was also using the wrong count for Total Tickets. I have updated both columns so now we have the Total Ticket column show the Total Ticket Count including comps. The No Comp Tickets and No of Paid Tickets when added together is the Total Tickets.
Here's the corrected Dashboard. If you prefer not to import a new dashboard all you need to do is go into the Total Tickets Value and change the Count to Sum ([Total Ticket Count]) in stead of Total Paid Count. And changed the Total Paid Count to Total Ticket Count in the No Paid Tickets Value formula. 5226.TST-DAILYWRAP2019-Corrected.dash