Hi James! You could probably do this (without having to specify exact blocks of time or force the 60 minutes to start at :00) by using table calculations to compare a record to the 2 previous records (arranged chronologically). However, since you'd have to build this in a sheet, it'd limit your flexibility on view layouts.
What calc or formula do you think you'd write if you wanted something to filter customers by, keeping those with 3+ in 60m, with just customer name in rows?
2 of 2 people found this helpful
Well, what I would personally do is drop Customer Name, Order ID, and Order Timestamp both into rows and then do something like:
IIF(DATEDIFF('minute', LOOKUP(MIN([Order Timestamp]), -2), MIN([Order Timestamp)) < 60, 1, 0)
This will give you tons of rows--one for each customer and order. The ones where the 2 consecutive orders before that order were within the last 60 minutes will have a value of 1. They'll be 0 otherwise.
Then, you can do a WINDOW_MAX() along each customer to figure out which ones have high order velocity.
Then, in order to hide all but one row per customer, you can use LAST()=0 as a filter.
Thank you, David. I'll try this out and report back!
was able to do what i needed with your help. tweaked some things (e.g., datediff), but this pointed me in the right direction.
You're welcome, James! If you wouldn't mind, could you mark the answer as correct so this doesn't show up as unanswered anymore? Thanks!