I want to do some more bucketing, but not sure how I can make it work.
We want to look at mobile sales for our General Admissions. All our sales are currently through TNEW. The only thing I could come up with would be to try to group things by the time between [Order Hour] and [Attended Hour].
However, when I try to bucket things (something like
)
it won't let me use flat Order Hour. It keeps pre-totaling things. I want to say: "Show me the count of tickets where [Attended Hour] is equal to [Order Hour]", or "Show me how many people bought tickets and then visited within an hour".
How could I make this happen?
HI Nathanael,
Some flavor of the following may work for you... grouping by SLI ID (so a per-ticket evaluation), if the attended hour = order hour, then add 1 to the SUM, else 0 or NULL.
SUM( [SLI ID] , IF ( MAX( [Attended Hour] - [Order Hour] ) = 0 , 1 , NULL ) )
This will not count an order at 59 minutes after the hour that attended 1 minute later. We could change it to bucket orders that attended in the same or next hour, but it's a trade off, as an order at 10:01am that attended at 11:59am would be counted.
SUM( [SLI ID] , IF ( MAX( [Attended Hour] - [Order Hour] ) >= 0 AND MAX( [Attended Hour] - [Order Hour] ) <= 1 , 1 , NULL ) )
Thank you!
It won't let me select [Attended Hour]- it keeps forcing it to [Total Attended Hour].
It seems to accept [Order Hour] as a second identifier, but not a first- when I use [Order Hour] for both, it then doesn't recognize [Order Hour]
Hi Nathanael,
Is it that it's not letting you select it without an aggregation from the Data Browser? Shown here as clicking More... and then All Items.
Or once it's in the formula, it's no accepting that dimensional value and is requiring an aggregation?
When I had typed in 'Attended Hour' and selected it, it defaulted to 'Total Attended Hour', and the same with 'Order Hour'. I hadn't realized about the More > All Items trick- thanks for that!
It wouldn't let me type out " [Attended Hour] ", it was requiring me to click on 'Attended Hour'
With that taken care of, it seems to be working!
To make sure I'm understanding what's going on- in this example, Analytics is saying "For these Modes of Sale, here are the total number of Sub Line Items (Where the Attended Hour = the Order Hour)". Or "These are all the SLIs for whih the owner bought, then attended within one hour".
Is that right?
Nathanael Pearson said:"For these Modes of Sale, here are the total number of Sub Line Items (Where the Attended Hour = the Order Hour)"
Yes.
Nathanael Pearson said:"These are all the SLIs for whih the owner bought, then attended within one hour"
Mostly. All these SLIs attended within one hour, yes, but there could be some SLIs with an attended hour = 1 + order hour, where the attendance was still within 60 minutes of the order, but where the attended hour <> order hour.
Fair enough. Thank you for your help, yet again! I appreciate it. If there's any related questions, I'll add to this thread.
I had a follow up question:
As I was fine-tuning the widget, I noticed that some of the orders it labelled as 'attended within 59 minutes of order time' were within the '0' Order Hour. As I looked these Orders up, none of them have been attended at all- like this one here:
The number of orders is quite low - three orders in total, so it won't have any weight on our numbers, but I'm wondering why those were picked up.
When a ticket is not marked attended, the Attended Date defaults to 1900-01-01, with Attended Hour = 0. Filter on Attended Flag = Y somewhere in your filter chain, either dashboard, widget or formula depending on what's appropriate for the scope of that filter's impact. If it has to be down to the formula, then the formatting I provided above would need to change to aggregate the Attended and Order Hours separately...
( MAX( [Attended Hour] ) , [Attended Flag=Y] ) - MAX( [Order Hour] )
I just added in a widget filter to omit 'Attended Year' 1900, and that did the trick. Thank you!