Using CASE Statements to hardcode footfall targets into Analytics

Hi all,

During our community meetup at TLCC, when we were discussing about how we measure success and the difficulty was raised around the fact it is not possible to put a daily footfall target into each Tessitura performance, to then pull into analytics for comparison. I mentioned that RCT have a way of tracking this in analytics and Melanie Ryan asked me to add it to the group so here it is!

For each of our venues, we have a daily footfall target, which we can then total into a monthly and yearly target. For example, here we've got the purple bars for the actual tickets sold, and gold for the monthly footfall target:

To put the gold bars into the widget, we have a series of case statements (hard coded in the analytics formula).

For example, this is what we have for the monthly chart, where [TARGET] is the number value that you want to set your monthly target to be:

CASE
WHEN MAX( [Calendar Month] ) = 4 THEN [TARGET] 
WHEN MAX( [Calendar Month] ) = 5 THEN [TARGET] 
WHEN MAX( [Calendar Month] ) = 6 THEN [TARGET] 
WHEN MAX( [Calendar Month] ) = 7 THEN [TARGET] 
WHEN MAX( [Calendar Month] ) = 8 THEN [TARGET] 
WHEN MAX( [Calendar Month] ) = 9 THEN [TARGET] 
WHEN MAX( [Calendar Month] ) = 10 THEN [TARGET] 
WHEN MAX( [Calendar Month] ) = 11 THEN [TARGET] 
WHEN MAX( [Calendar Month] ) = 12 THEN [TARGET] 
WHEN MAX( [Calendar Month] ) = 1 THEN [TARGET] 
WHEN MAX( [Calendar Month] ) = 2 THEN [TARGET] 
WHEN MAX( [Calendar Month] ) = 3 THEN [TARGET] 

ELSE 0
END

You would have a case statement per month of the year, which is then transposing the target to the appropriate month on the chart. 

We then have the same for our daily target:

Example formula for the daily target:

CASE

WHEN [Total Calendar Day Of Year] = 4 THEN [TARGET]
WHEN [Total Calendar Day Of Year] = 5 THEN [TARGET]
WHEN [Total Calendar Day Of Year] = 6 THEN  [TARGET]
WHEN [Total Calendar Day Of Year] = 7 THEN  [TARGET]
WHEN [Total Calendar Day Of Year] = 8 THEN  [TARGET]
WHEN [Total Calendar Day Of Year] = 11 THEN  [TARGET]
WHEN [Total Calendar Day Of Year] = 12 THEN  [TARGET]
WHEN [Total Calendar Day Of Year] = 13 THEN  [TARGET]
WHEN [Total Calendar Day Of Year] = 14 THEN  [TARGET]

ELSE 0
END

Where [Total Calendar Day of Year] is the numbered day of the year, out of 365. We have an excel spreadsheet which maps the target to the day of the year so we're not sitting there for ages writing the case statements out! The only issue with the daily widgets is that CASE statements have a limit per widget (I think around 150), which means you can only put targets in for part of the year. We get around this by putting in the targets for the next few months, and once those months have passed, add in the new targets for the next three months and so on.

This is obviously very time intensive, so having a field directly in Tessitura where we could add in our target would be the ideal solution, but we've found this works quiet well for us without the need for creating anything custom.

Ellen