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

This is amazing! I had a similar question in a different thread, but Nathanael was able to articulate it much better.

Would there be a way to sum this by households? So instead of seeing the number of orders, I could see households?

For example, if someone purchased 4 tickets to one show, they would currently be counted twice in the 2 ticket/ order column.  Is it possible to see them listed once under the 4 ticket column?

Yes Kari,

Change the pre-aggregate grouping from Order ID to Constituent ID... in which case we're counting Constituents rather than Orders.

SUM ( [Constituent ID] , 
   IF ( [Total Ticket Count] = 1 , 1 , NULL )
)

Yes Kari,

Change the pre-aggregate grouping from Order ID to Constituent ID... in which case we're counting Constituents rather than Orders.

SUM ( [Constituent ID] , 
   IF ( [Total Ticket Count] = 1 , 1 , NULL )
)