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.
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?