• Replies 11 replies
  • Subscribers 961 subscribers
  • Views 651 views
  • Users 0 members are here
Related

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

Nathanael Pearson
$organization

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.

Parents
  • $organization

    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

  • $organization in reply to Chris Wallingford

    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?

Reply
  • $organization in reply to Chris Wallingford

    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?

Children