I'm having trouble making a pivot table that will show me the running sum of our sales broken out over order days prior to opening, next to the season goal amount, and then track the running sales to goal. Here's what I'm trying to see:
Order days prior to Open Total Paid/Reserved Value Goal % to Goal
-4 250,000 1,000,000 25%
-3 500,000 1,000,000 50%
-2 750,000 1,000,000 75%
-1 1,000,000 1,000,000 100%
Has anyone build anything like this? What is the trick to getting the goal to stay static (based on budget amounts for all the performances in one season)?
I've tried suggestions I've found in other posts (like this one), but the goal amounts shift over each day prior to opening.
Any help is always greatly appreciated.
Thanks,
Michael
The 'Goal' amount could be a hard-coded 'Formula' that you just type a number for. You can create a 'Formula' and set it to '$1,000,000', and boom, you're done. The Total Paid/Reserved would update, and you could pull that into relationship with the $1,000,000 as you need to. Once you set the formula to a static number, it will stay that way for each Row/Value intersection.
In this iteration, the 'Goal' would have to be the same number across the Rows. You could do fancy things with CASE/WHEN statements (paging @chriswallingford or Heath Wilder ), but this iteration's strength is it simplicity.
If you're not hardcoding your goal and it's instead coming from the Performance Budget Amount, this post talks about that a bit:
(+) Rolling Sales as they approach budget goal - Reporting & Analytics - Forums - Tessitura Network
And a formula like this for the Goal:
MAX ( [Order Weeks Prior to Open] , SUM ( [Performance ID] , [Max Budget Amount] ) )
Nathanael Pearson Thank you! Your initial suggestion got me started on a solution to my problem. And thank you for paging Chris to take a look as well!
Chris Wallingford Ultimately your suggestion works best for comparing progress compared to last season easily. Thank you for chiming in with your expertise!
Thanks again to you both! Need to remember to reach out to the forums sooner, and save myself some headaches.
This is the way
Heath - bonus points for a perfectly-timed Mandalorian reference!
Hi Michael,
There was a discussion about something similar a few months ago that you might also be interested in looking at:https://community.tessituranetwork.com/topical_groups/analytics-coffee/f/discussions/30795/sales-pacing-chart---inputting-goals-budgets
The example relating to ticket counts, but it can easily be changed to sales and the budget amount can be used instead of hard-coding a goal. It references comparable productions to create an index value pacing towards the goal.
I hope the example in there helps!
This is an amazing resource. Thank you for referring me to this discussion, Christine!
The link to the walk through with Christine Wingenfeld is in the analytic coffee! files here
https://community.tessituranetwork.com/topical_groups/analytics-coffee/m/files/57683