How To: Count of Orders with only 1 ticket, only 2, and 3+

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

Hi Chris (or anyone else who may have some insight!),

I am attempting to follow this equation, but receiving the following error: "Function Syntax Error: Error in function definition (Sum): Expecting parameter of type 'Set Expression' but found 'Member Expression'"

Here is a screenshot of the function. What am I missing here?

Thanks to the new pop up skill builder, I am hoping to use the new and improved version, but experiencing the same issue.

Thanks,
Joe

Hi Joe,

Click on that [Total Order ID] and change the Type from a SUM to All Items. 

Best,
Chris

That did the trick. Thank you! 

That did the trick. Thank you!