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

Hello Nathanael,

The concepts you are looking for are Filtered Values, Bucketing, and the ALL function. Here is a link to a webinar "Take Your Analytics Skills to the Next Level."

https://www.tessituranetwork.com/en/Items/Videos/Webinars/2019/Tessitura_Analytics_Skills

Filtered Values and Bucketing are covered starting at 7:20 into the video. You can also download the Power Point presentation (I recommend you download it as a quick reference document). Bucketing should allow you to do your counts into separate buckets of orders with 1 ticket, 2 tickets, 3+ tickets.

To get the percentages of all orders, you would need to use the ALL function.

Here is an example of using the ALL function:

Here is the Percent of Tickets formula in a easier to read format.

SUM([Ticket  Count]) / ( [Total Ticket Count], ALL([Comp Reason]) )

A few notes about the formula:

1) SUM([Ticket  Count]) is the same as [Total Ticket Count]; Sisense (Analytics) sometimes shortens the equations when it can.

2) Notice that in my example the ALL function uses [Comp Reason]. For your equation, you would use whatever you are grouping the tickets by in your equation (probably Production Season).

3) I would suggest starting with a Pivot Table to make sure that everything looks correct before changing the widget type.

Thank you, this has gotten me closer! I've watched the video, and reviewed the power point.

I must confess I'm stumped. I can't see how to filter by the amounts of tickets within an order itself. I can filter [Total Ticket Count] by [Production Season], which helps me to whittle things down, but I can't see how to filter by the sum of tickets in an order. I feel like I'm missing something.

I can get the sum of all tickets within a production season, but I can't see how to show all orders( that have 1 ticket in them) within a production season. 
I feel I can almost get there.

When I set this up, the displayed numbers don't change- they remain the same as if I was just displaying the Production Season filtered formula. I know that isn't right, because there's many orders with more than one ticket inside.

In essence, how do I get [Ticket Count] to look only within orders, and not within a production season?

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.

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 )
)

Thank you to both of you! This is quite helpful. 

I did have a follow question: when I run those numbers for our new Timed Admission Production Season, the numbers turn out like so:

Those numbers can't be correct, because there's been only 1812 total orders in that Production Season so far.

If the # of unique orders within the Prod Season are correct the number of orders with >=3 tickets should be 681, not 3528.

Would it be a COUNT vs SUM thing? When I swapped that out, each SUM-IF grouping yielded '1' as the answer, each time.

I think I figured out what I did!

I changed both the instances of each integer (1,2,3) in the formulas, so it counted each order as 2 for the 2-counts, and 3 for the 3 counts! 

SUM ( [Order ID] , 
   IF ( [Total Ticket Count] >= 3, 1 , NULL )
)

When I adjusted those formulas (I'd simply not been careful enough with copying), the numbers came to 1811, and the percentages came to 1.

Thanks again!

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!