Calculating Transaction Time between one transaction and another with only a start time

[Jholiday78]

Greetings,

In my ATM transactional data set (one transaction per row) I have the ATM ID, the customer id, and a start date/time for a transaction in one row.  I can assume that if another transaction occurred within 10 seconds to 5 minutes of the first one, that it is all part of the same transaction (e.g., a customer makes a deposit and then a withdrawal, then the transaction ends).  

How can I find the time difference for these if they fall within 10 seconds to 5 minute time frame and not count a transaction that may occur the following day by the same customer?

I was thinking the multi-row formula, but I don't know what formula to input.  And I don't have an end date/time field.

My ultimate goals is to figure out the average amount of transactions that occur from when you start and finish using the ATM.

Thanks in advance,

Jessica

[DEllis]

First you’d want a field for the date part only, then for the multi row formula you’d group on the date, atm, and customer ID and the formula would be a datetimediff for transaction time and row-1 transaction time. Then you’d just filter out the time differences that don’t meet your criteria. Hope that helps. 

[binuacs · Accepted solution]

@Jholiday78 I added some additional records in the input file for more understanding of the requirement.

1. Find the difference in seconds using the multi-row formula tool

2. Calculate the count using the multi-row formula where the time difference > 300 seconds (10sec - 5 mints range)

3. calculate the sum of the counts using the summaries tool will give you the result

[Jholiday78]

@binuacs This seems to work perfectly.  Thank you for the help!