Hello,
First time posting. I'm attempting to identify transactions that occur within 10 minutes of one another. The report is automatically refreshed every few minutes and the data is constantly changing which has created some challenges. As an example, in the table below I'd like to "flag" the last 4 entries (between 2:53:42 and 2:55:13). I used the running-difference function to calculate the minutes between and then added a conditional style to highlight each entry when the minutes between is between -1 and 10. Unfortunately this approach excludes the first entry (2:55:13) since the prior transaction occurred hours after. Does anyone have any thoughts on how I can accomplish this?
Date Time Minutes Between
Mar 3, 2021 7:21:35 PM Null
Mar 3, 2021 2:55:13 PM -266
Mar 3, 2021 2:54:44 PM -1
Mar 3, 2021 2:54:15 PM 0
Mar 3, 2021 2:53:42 PM -1
I assume your running-difference looks something like...
running-difference (datediff({minute}, 2020-01-01, [EventTime]))
...since running-difference wants numbers, not datetimes.
One possibility:
Let's call the current query "forward".
Sort primarily by the datetime in ascending order.
Copy "forward" to "reverse".
Rename "Minutes Between" to "minutes since last" in "forward" and "minutes until next" in "reverse".
Sort "reverse" primarily by the datetime in descending order.
Create a third query "output" and join "forward" and "reverse" into it.
Add all fields from "forward" and the datetime field from "reverse" into "output".
(this may take another downstream query) Filter on "minutes since last" < 10 or "minutes until next" > -10.
Make the list use "output".
If you have some matching datetime values, you'll need to sort by an additional column to increase predictability.
Interesting idea, thanks for the reply. I'll give a try and will let you know how it works out.