Hello!
I've run into a problem with tracking our "Running Sales % to Goal by Days to Closing". This is a new dashboard widget for us. Our new Managing Director used it at his previous theatre. But- it's not working for us. Here is how it's looking now.
I think the problem is- we enter our "Budget" at a per performance level. So- let's say we want to make $100 on our run of "Example Play" over 50 performances. We would enter $2 as the budget in production elements -> performance. Where his previous theatre would enter $100 on each performance.
I think that the "Order Days Prior To Closing" tool is skipping days where there were no sales for a given performance. So it's also not pulling in the budget from those performances either-- making the budget change every day.
Note- we're also using a budget formula I believe my predecessor Katie got from Chris Wallingford.
sum ( [Performance ID] , max ( [Budget Amount] ) )
I pulled a pivot table that shows the problem from a different angle. https://drive.google.com/file/d/1j3FllhGVll8t4xPBdp652CYDNicMk9gi/view?usp=sharing
Help?
You're correct Mike.
Within each Order Day, only the budgets of the performances for which there are sales will be included. Therefore we need a formula to get the overall budget that also ignores any given Order Day we may be in. Here's a post that outlines it a bit:
(+) Rolling Sales as they approach budget goal - Reporting & Analytics - Forums - Tessitura Network
The formula uses the formula that you already have, but looks at that formula across all Order Days and takes the budget from the Order Day that has the greatest budget. Unless there's a completely sold out show or you've filtered out unsold seats from the scope of the widget, then the 0 days prior bucket has all the unsold seats in it, and therefore all performances should be represented there.
( MAX ( [Order Days Prior to Performance] , SUM ( [Performance ID] , [Max Budget Amount] ) ) )
Here's a screenshot of a Broadway series of productions and performances and their respective budgets. Then in another pivot, the typical budget formula you already have. Then next to that, this enhanced version of that formula showing the expected overall budget on every row.
Hope that helps!Chris
Thank you!!