Rolling Sales as they approach budget goal

Hi Anna,

This is a sticky one because in order to avoid inflation of the Performance Budget Amount field, we typically use multi-pass aggregation, and group the Budget by Performance ID prior to taking a SUM()...

( SUM ( [Performance ID] , [Max Budget Amount] )

The other technique we have in our toolbox is the ALL() function, to break out of however we're grouping the data in a widget and get access to more holistic numbers, for example if instead of Budget Amount, you were after the % of the overall paid amount at the end of the curve...

RSUM ( [Total Ticket Paid Amount] ) / ( [Total Ticket Paid Amount] , ALL ([Order Weeks Prior to Performance ]) )

Where we hit on a challenge with Budget Amount is we can't easily break out of our Order Weeks for that Performance ID we're grouping by. We can only apply the ALL() function to an aggregation, and not to that "group by" field. I have 2 ideas for you to try.

If there exists in the data, reliably, at least one week in which all events have sales, then we could look for the week that has the MAX Budget Amount and render that on all weeks.

MAX ( [Order Weeks Prior to Performance] ,
    SUM ( [Performance ID] , [Max Budget Amount] )
    )

But if there is no week in which all events are represented, this value will under-report. We could instead embrace the row-inflation and try something like this:

( [# of unique Performance ID] , All([Order Weeks Prior to Performance]) ) *
( [Total Budget Amount] , All([Order Weeks Prior to Performance]) ) /
( DUPCOUNT([Ticket Count]) , All([Order Weeks Prior to Performance]) )

The number of events, times the inflated budget amount over all weeks, divided by a row count. A quick test seemed to return correct results even with a mix of distinct budget amounts.

I'm attaching my work to simplify the formula construction for you, that way you can copy / paste the formula after importing the dashboard and testing it against your data.

RSUMAgainstGoal.dash

Hi Chris,

This is very helpful - thank you so much for taking the time to share this dash! I've tried all of the formulas you provided and am finding that the "embrace the inflation" formula works best for different combinations of performances/production seasons but if I choose the entire season it will embrace far too much inflation! I wonder if it's possible to combine it with a case statement that just sees total budget if full season is selected in the filters? Not sure that's possible?

Hi Chris,

This is very helpful - thank you so much for taking the time to share this dash! I've tried all of the formulas you provided and am finding that the "embrace the inflation" formula works best for different combinations of performances/production seasons but if I choose the entire season it will embrace far too much inflation! I wonder if it's possible to combine it with a case statement that just sees total budget if full season is selected in the filters? Not sure that's possible?

Hi Anna,

I expanded my filters to an entire season and see what you mean. Thanks for working on this with me. I've actually been trying to solve particular use case for a while, and might have it. Would you try this? I'm reattaching the dashboard sample.

There's a new column in the bottom widget called Wk=0. It's the same formula as [Only Max from Weeks], however, I've tried setting a filter on the [Order Weeks Prior to Performance] field to only return the value from week = 0. That should work even if week 0 is filtered out of the dashboard or widget. It may not work as is if Unsold seats are filtered out.

The idea being that we need to find and report on a week in which all events are represented. For unsold seats, we stamp 0 into that [Order Weeks Prior to Performance] field. So assuming that all events have at least 1 unsold seat (or that if they're sold out, they have sales in week 0), then week 0 should have all events. 

RSUMAgainstGoal.dash

Hi Chris,

That seems to have done it, thank you! I did need to change my chart to order WEEKS prior to performance instead of order DAYS prior -- I originally tried to use your same equation by just changing that one criteria but it really doesn't want to work with days. This will work for our purposes, though, and I really appreciate the help!

I'm sad to hear that DAYS didn't work. May I have an export of your dashboard?

Just popping in the middle of this to say YESSSSSSSS! I changed it over to Order Weeks Prior to Opening and this is super helpful to be able to show on a graph instead of just an indicator widget! Thanks, Chris, and thanks, Anna, for bringing the topic up.

Of course, here's the dash. The DAYS feature seems to work fine for most events but seemed to be worst with events that ran for multiple days. Most of our events are one-offs but for any that have multiple performances we'd want to be able to look at the chart based on the full production season or one performance at a time, with the goal reflecting either the total for the full production season, or the single performance's goal (respectively).

 BRD-ExamplePresentationDashboard.dash

Thank you for sharing and for meeting with me today, Anna.

Just to close the loop in this thread, we confirmed that for at least one show, this formula isn't working at the Days grain. The example was a series of small capacity events with several sell-outs. Thus, there was no "day" on which all shows were represented - not even Day = 0 which is associated with any unsold seats.

You proposed that if the Weeks grain didn't work for you, you could work around the issue by either including Blackout Holds in Analytics (T_DEFAULTS setting) or by adding one, held, unsold seat to each performance. Either of these workarounds could unexpectedly increase Analytics capacity values if not filtered out, but would also ensure that every event was represented with an unsold seat in Day = 0, which would then ensure that at least Day = 0 would return the full capacity of the event series.

We had a similar issue with events that had multiple performances and this is the formula our wonderful Tessi reps came up with that worked:

( [# of unique Performance Date and Code Short] , [Days in Date] ) * [ Max Budget Amount]

The issue I am having now, if anyone can help with a solution, is track a daily sales target based on that budget and days prior to the performance. So I need Budget - Sales to date / Days prior to performance (which I'm currently using Order days). Just working with a Pivot table to start and the numbers aren't working out. Any ideas?

Hi Hillary,

I don't 100% follow where you're going, but I think I can get you started with a daily accrual of your budget target over Days Prior to Performance.

The exact formula depends a little on your widget, but we need to take the budget amount, divide it by the number of periods in your widget, and then multiply that by the ordered rank of each period in the chart.

For example, since you are looking at this over Days Prior to Performance 

( MAX ( [Order Days Prior to Performance] ,
   SUM ( [Performance ID] , [Max Budget Amount] )
   ) 
  / ( [# of unique Order Days Prior to Performance] , ALL( [Order Days Prior to Performance] ) ) 
)
* ( ORDERING([Min Order Days Prior to Performance]) + 1 )

With that you can either divide an RSUM of your Total Ticket Paid Amount to see the Paid Amount % of the running Daily Budget Target, or you can subtract the RSUM of your Total Ticket Paid Amount from that running Daily Budget Target to get a Daily Sales Target.

Best,
Chris