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

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

Count of Transactions_Alteryx Sample.yxmd

Date Time

Accepted answers

binu_acs

@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

Count of Transactions_Alteryx Sample.yxmd

All comments

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.

binu_acs

@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

Count of Transactions_Alteryx Sample.yxmd

Jholiday78

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

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235