• 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

    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

Reply Children