How to Date Diff across Two Dates Spanning Days

I am trying to find the amount of time that elapsed from the creation of a transaction till its completion. In other words, this should measure the amount of time waiting in the queue to be processed + the processing time for that transaction. I have a solution that works in many but not all cases.

From the QueueItem table I am using this formula as a value in my table:
MnDiff([Minutes in EndProcessing],[Minutes in CreationTime])

This works well but fails in the case that the EndProcessing date is on the next day from the CreationTime date, in which case it returns an incorrect negative number. Is there some way I can simply subtract the entirety of the two dates so it will account for changes in the day? It seems a group by is required, even though its always only grouping a single record.

Suggested approach should be to have the start and end time as the datatype of datetime and then simply use the subtraction of those two variable and at the end add .TotalHours

.TotalHours
This will give you hours.minutes format in string datatype, If you use without this, you’ll have the output as timespan datatype

Hope this helps!

The two fields I am using are the default queue item fields. I would ideally use only default fields so I can report on SLA/response time for all queues generically, without the need to add any custom reporting fields at the process level. When I add the fields to the function text box, it requires me to select a time group by option. I don’t see any way to operate on it as a datetime in order to take a difference between the two dates. Are you saying there isn’t a way to calculate this metric with default fields directly in Insights?

Basically what I want is for each queue:

  • Avg/Med/Max transaction wait time before processing begins
  • Avg/Med/Max transaction processing time ([Transaction Duration In Seconds] default field works for this)
  • Avg/Med/Max total time to process transaction from creation in queue to completion

This is a very high level performance report that shouldn’t require any special data exports at the process level.

I would take a look at the data model docs here: Insights Data Model

  1. How would you calculate the wait time before processing begins? Is this the time the queue item was created plus the time until the robot started processing the queue item? If so, both of those fields come out of the box and are found in our data model docs above.
  2. Yes, that is correct.
  3. In the data model, we have the ability to see the creation time (time the queue item was created). Then, you can leverage the Endprocessing field (the time the robot finished processing the queue item).