Sales Pacing Chart - Inputting Goals/Budgets

Hey Michael! Here is a recent conversation that I think would help putting in custom dates/goals: https://community.tessituranetwork.com/topical_groups/analytics-coffee/f/discussions/29251/difference-to-date-in-an-indicator-widget/81104#81104

Particularly Nathanael Pearson and Chris Wallingford's exchange at the end. 

Not sure it's exactly what you're looking for, but kinda related to get you in the right direction?

hope that helps!

Hi Michael,

I have a widget with some formulas that might be similar to what you are trying to accomplish. It involves hard-coding numbers for goals into the formulas and determining comparable productions from previous years. 

In this widget below, the goal is a calculation based on past sales and the production's goal and then the orange line is the actual sales. Since the gray bars use past years' data they'll appear even if the current sales aren't to that week yet.

The dashboard itself is filtered to select a current year production and three comparable productions from prior years.

The formula I have for the Goal bars is:

(RSUM(([Total Ticket Count], [Season Fiscal Current Year Offset])))/(( [Total Ticket Count],ALL([Order Weeks Prior to Closing]), [Season Fiscal Current Year Offset]))*5950

The season fiscal year offset filtered values exclude "0" (the current year), so using the productions selected on the dashboard from prior years, it creates an index of where you typically would be to total sales by this week in time. Then the hard-coded number in there is the single ticket goal for this year's production.

Then for the Current Year, the formula I have is:

RSUM(([Total Ticket Count], [Season Fiscal Current Year Offset]))

And the season fiscal year offset is set as including only "0" for the current year.

You could use filtered values to select the specific productions instead of going the fiscal year route. And switching ticket count to total paid amount would give you sales. I liked to have a ticket tracking widget next to my sales one for comparison.

Though if you want a more simple route, something that comes to mind is just hard-coding your goal as a straight line in your widget. So your formula would just be the goal amount. And then you could have another line or bars for actual sales. I think there is another example of this somewhere in the forums, but here's a quick idea:

I hope that helps or at least gives you an idea to work with!

~ Christine

Hi Christine, 

Thank you for this thorough run through of your widgets! The first one is definitely more along the lines I'm talking about, so I will play around with that formula. 

Thanks!

Thanks Brian! Much appreciated!

I do the latter ... but this week by week tracker my friend is a thing of beauty.  Definitely stealing that idea (with attribution).

This is exactly the thing that my former colleague was mad that Analytics couldn't do and you've figured it out! Very cool, thank you for sharing!

Hi Christine! 

I am hoping to get a little more clarity on how this formula works. Without the hard coded number, what is the equation representing? 

I'm finding when I apply the same to sales, with the budget goal as the hard coded number, it is giving me figures in the millions (which I would LOVE to have for a single concert, but alas...) 

Here is the graph I get with the formula with my changes

(RSUM(([Total Ticket Paid Amount], [Production Season1])))/(( [Total Ticket Paid Amount],ALL([Order Weeks Prior to Closing]), [Production Season1]))*5000


*5000 being a placeholder figure for purpose of sharing.


After seeing this I used the Quick Function - Running Sum by Category and got this



I know you said you included multiple past FYs to be a basis for projecting, but it seems this might be adding too many previous ones together? 

Forgive me if this is a simple fix, I'm sure my thought process is cloudy with this dashboard haha.

Thanks again for any insight you're able to offer

I'm going to do a live build of this in Analytic Coffee tomorrow if anyone want's to come watch.

Hi Michael,

I'll see if I can explain a little more and if that helps. You shouldn't need to use the Quick Function since the running sum piece is built into the formula.

So the core part of the formula is essentially creating an index that based on the previous seasons' productions, identifying that we're usually at X% to our total sales by X weeks prior to closing.

In this numerator portion, we're looking at a running sum of the total ticket paid amount filtered to look at previous production seasons.

(RSUM(([Total Ticket Paid Amount], [Production Season1])))

Then this is divided by the grand total of sales for those production seasons to create a percentage by week. Using the "all" function ignores the use of the weeks prior in your widget.

(( [Total Ticket Paid Amount],ALL([Order Weeks Prior to Closing]), [Production Season1]))

If you were to isolate that portion of the formula before you add the goal it should be a percent. You could start with a pivot table as a way to check that or to help break down each of the pieces.

Then by multiplying this percentage by your current year's goal, it gives you an estimate of where you should be pacing towards the total by week.

One thing to maybe check is that your widget is using the same order weeks prior to closing, the many options for weeks has tripped me up before.

~ Christine

Thank you, Christine. 

I thought I should be getting the percentage, but for some reason just the equation alone is giving me numbers > 1., so when multiplied by any goal, it inflates it drastically. 

I will keep working around this. At least I know I'm not crazy! I appreciate your time and help.