Group transactions that occurred within a 30 second time frame

[elclark]

Hello - I'm trying to group transactions that occurred within 30 seconds of each other to aggregate transaction amounts. I've attached sample data to show what my input looks like and what I would like my output to look like. The colors don't mean anything and aren't needed, I just added it to help visualize the transactions that would be grouped together. Any help would be much appreciated. Thanks.

[ShankerV]

Hi @elclark 

Please find the expected output.

From the above output, if you need to merge cells like below in the excel sheet.

Please add the below steps to my existing workflow to merge the cells on the output.

https://community.alteryx.com/t5/Engine-Works/How-to-Create-Merged-Cells-in-Reports-Part-1/bc-p/9757...

The above article created by @atcodedog05 helps to merge the cells.

Many thanks

Shanker V

[elclark]

Hello - thank you for the help, so I'm running into an issue where the multi-row formula is not accurately assigning a 1 or 0 in a couple of instances. I've attached my workflow and you can see between records 5/6 and 22/23, there is more than a 30-second time difference but it still assigned a 1 instead of 0. Do you know what could be causing this? Thanks.

[davidskaife · Accepted solution]

Hi @elclark 

It's due to the dates; in both instances the second date time is before the first, so the results are negative values and triggering as true:

One way of rectifying is to tweak the formula to this, essentially saying it must also be at or above 0:

IF datetimediff([DateTime_Out],[Row-1:DateTime_Out],"sec")<31 AND datetimediff([DateTime_Out],[Row-1:DateTime_Out],"sec")>=0
THEN 1
ELSE 0
ENDIF