I am trying to get a count of people who came to the museum, not tickets. We currently aren't using combo tickets so we have GA tickets and Omni tickets. I can do the formula if I pull this into excel but is there a way to do this in analytics? I basically am saying in an order, if GA tickets are equal to or greater than Omni tickets use the GA ticket count, if not use the Omni count. Any ideas? I am happy to abandon this whole idea if someone has a better way to count people in the door.
Just use a CASE statement with Filtered Values. The Filtered Values concept is covered in the documentation and also shown in the "Take Analytics to the Next Level" webinar video on the Tessitura website.
Here is the concept:
1) Formatted as a CASE statement
2) Formula parts are inside of parenthesis
CASE WHEN ([Total Ticket Count], [Filter1 Field], [Filter2 Field]) > ([Total Ticket Count], [Filter3 Field], [Filter4 Field]) THEN ([Total Ticket Count], [Filter1] Field, [Filter2 Field]) ELSE ([Total Ticket Count], [Filter3 Field], [Filter4 Field]) END
The basic concept is like this:
CASE WHEN (Count of Thing 1) > (Count of Thing 2) THEN (Count of Thing 1) ELSE (Count of Thing 2) END
Here is the link to Filtered Values:
https://www.tessituranetwork.com/Help_System/Content/Tessitura%20Analytics/Designing%20Dashboards/Filtered%20Values.htm
Here is the webinar link:
https://www.tessituranetwork.com/en/Items/Videos/Webinars/2019/Tessitura_Analytics_Skills
Thanks Neil! this was helpful yet I am still not getting the desired result. Total attendance should pull in the GA number if it is greater than or equal to the omni number but it isn't pulling in. This only seems to be an issue when Omni is 0. Any ideas?
Try adding additional WHEN’s before the ELSE.
The “>=“ part should take care of the 0 issue. How about group by Performance Date instead of Order ID.?
Nothing seems to be working. Bummer. So close!
Export the .dash file out of Analytics and email it to me.
neil.cole@state.mn.us
Do these performances have anything in a common; a Season, Production Season, Production, Title, or performance type?
Also, in general, you should always start with a Pivot table to make sure you are looking at the correct data. I'd suggest a pivot Performance date for the Row and 3 Values (GA Ticket Count, Omni Ticket Count, and your conditional Ticket Count). This way you can see if your formula is working correctly.