I'm looking to do a bit of formula work in Analytics.
I want to see how many (and if possible what percentage of total orders) have one ticket within, how many have 2, and how many have 3+. I'm fiddling around with [Total Ticket Count], but I can't seem to find how to ask:
"How many orders (within this Production Season) have [1 (and only 1)] ticket?"Once I have this in place, I imagine I can adjust for [2 (and only 2)] tickets, and [3+ tickets].
I'd want something like "Sum[Order Count], WHERE [Ticket Count] = 1", but my SQL syntax is rather limited, and i'm not sure how to do that.
My fluency with CASE WHEN is even more limited.
Hi Nathanael,
This is an interesting one, and Neil is correct that we'll need to do some bucketing to get at these figures. Ultimately, the formula style we'll use for this is:
SUM ( [Order ID] , IF ( [Total Ticket Count] = 1 , 1 , NULL ))This is going to create a SUM of 1s and NULLs (or you could do 0s) where if for a given Order ID the SUM ( [Ticket Count] ) = 1, then 1, otherwise, NULL/0. So it's grouping the Total Ticket Count by Order ID before taking the final SUM, and instead of returning a Ticket Count to the SUM aggregation, is returning either a 1 or a NULL.
Apply the formula and name it what you like, like 1 Ticket. Duplicate the formula and edit, changing the = 1 to = 2, and then apply and rename that formula. For the 3+ bucket, change it to >= 3. If you wanted to do a bucket of 3-5 and then 5+, the 3-5 would look like:
SUM ( [Order ID] , IF ( [Total Ticket Count] >= 3 AND [Total Ticket Count] <= 5 , 1 , NULL ))
To turn those into % of Total Orders:
SUM ( [Order ID] , IF ( [Total Ticket Count] = 1 , 1 , NULL )) / [# unique Order ID]
Best,Chris
Thank you to both of you! This is quite helpful. I did have a follow question: when I run those numbers for our new Timed Admission Production Season, the numbers turn out like so:
Those numbers can't be correct, because there's been only 1812 total orders in that Production Season so far.
If the # of unique orders within the Prod Season are correct the number of orders with >=3 tickets should be 681, not 3528.
Would it be a COUNT vs SUM thing? When I swapped that out, each SUM-IF grouping yielded '1' as the answer, each time.
I think I figured out what I did!
I changed both the instances of each integer (1,2,3) in the formulas, so it counted each order as 2 for the 2-counts, and 3 for the 3 counts!
SUM ( [Order ID] , IF ( [Total Ticket Count] >= 3, 1 , NULL ))
When I adjusted those formulas (I'd simply not been careful enough with copying), the numbers came to 1811, and the percentages came to 1.
Thanks again!