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
Brilliant! This shows us how to do an equivalent of an Excel SUMIF function in Analytics. I knew there would be issues with [Ticket Count] because the only two available values are 0 or 1. Without the "SUMIF" part, you cannot bucket until after you aggregate. The combination of "SUMIF" allows for the bucketing to happen before the aggregation; which is exactly what we need in this situation.
Chris's solution combines the SUM function (with a group by clause) and an IF function.
SUM ( [Order ID] , IF ( [Total Ticket Count] = 1 , 1 , NULL ) )
SUM ( <group by field>, <aggregation> )
The “<aggregation>” part of the SUM function gets replaced with an IF function with an aggregation inside of it.
IF ( <condition>, <numeric expression 1>, <numeric expression 2> )
<condition> = [Total Ticket Count] = 1
*Side note, [Total Ticket Count] is the same as SUM([Ticket Count])
<numeric expression 1> = when <condition> is true = 1 in this example
<numeric expression 2> = when <condition> is false = NULL in this example
Thanks Chris, I'll add this to my bag of Analytics tricks.