Time Difference between different rows given specific criteria

[Draymora]

Hello all,

Been trying to figure out the best way to approach this - I am trying to get the time difference (minutes) between end time of each event A row compared to the start of event B within the same day & for the same person. Below is some dummy data with an added column of what I'd hope to get:

entry	Person	Date	Start	End	Event	Desired Result	how calculated
1	X	3/1/23	3:15:35 PM	3:30:00 PM	A	200 mins	diff of end time entry 1 and start time of entry 3 (event B)
2	X	3/1/23	4:50:45 PM	5:05:00 PM	A	105 mins	Time diff of entry 2 end time and entry 3 start time
3	X	3/1/23	6:50:00 PM	7:10:00 PM	B	N/A	N/A
4	X	3/1/23	7:30:00 PM	9:00:00 PM	A	20 mins	Time diff of start time entry 4 and end time of entry 3
5	X	3/2/23	4:00:00 PM	4:30:00 PM	A	30 mins	Time diff of end time entry 5 and start time of entry 6
6	X	3/2/23	5:00:00 PM	5:40:00 PM	B	N/A	N/A
7	X	3/2/23	7:00:00 PM	7:20:00 PM	A	80 mins	Time diff of start time of entry 7 and end time entry 6
8	Y	3/1/23	1:00:00 PM	1:31:00 PM	A	209 mins	Time diff of end time entry 8 and start time of entry 9
9	Y	3/1/23	5:00:00 PM	7:00:00 PM	B	N/A	N/A
10	Y	3/1/23	7:01:00 PM	7:30:00 PM	A	29 mins	Time diff of start time entry 10 and end time entry 9
11	Y	3/1/23	8:30:00 PM	10:00:00 PM	A	90 mins	Time diff of start time entry 11 and end time entry 9

[martinding]

Hi @Draymora,

How is this row calculated? 

X

3/2/23

7:00:00 PM

7:20:00 PM

A

80 mins

There doesn't seem to be a corresponding event B that starts after the end time of A.

And similarly:

X

3/1/23

7:30:00 PM

9:00:00 PM

A

20 mins

Y	3/1/23	7:01:00 PM	7:30:00 PM	A	30 mins
Y	3/1/23	8:30:00 PM	10:00:00 PM	A	60 mins

[Draymora]

The 80 mins is the 5:40 pm end time of event B to the time the event A starts at 7pm on 3/2 for person X. Trying to get the desired calc

[Draymora]

It’s the time difference of the end event B to the start of each A if B is before A, the end of event A to the start of event B if A is before B on same day. Event As could be before or after B. Apologies my desired calc I was trying to mental math the difference. There’s no formula in alteryx currently I’m trying to get the calc 

[Draymora]

The 80 mins is the 5:40 pm end time of event B to the time the event A starts at 7pm on 3/2 for person X. Trying to get the desired calc

[Qiu]

@Draymora 
I am also a bit confused with the logic here.
Maybe you can add one column to demonstrate how the "Desired Result" is calculated for each row. 

[Draymora]

Added additional columns with details 

[martinding · Accepted solution]

Hi @Draymora,

I think this is matches the expected logic:

But note for the last 2 rows, I think the original Desired Output contains errors.

WARNING: This is not a pretty workflow, and I hope someone can make it more efficient, but it should be the logic you are looking for.

[Draymora]

Thank you for the help! mostly worked - just had to tweak one or two things :) (it kept truncating the before and after lunch fields for some reason). I was wondering - would I be able to use something similar to determine if multiple event As happened back to back as well? (seeing the time difference for these too, same thing with it being within the same day & for each person)

[martinding]

Hi @Draymora,

Yes! In fact, it would actually be easier to do it back to back.

So you just need to sort by:

• Entry No. (If there is such a field and it is in order)

Or

• Person - Ascending
• Date - Ascending
• Start Time - Ascending

And perform the Multi-Row calculation:

• DateTimeDiff([Start Time], [Row-1:End Time], "minute")

This is calculating the difference in minutes between the End Time of the last event (previous row) and the Start Time of the current event (current row). No field needs to be grouped by in this case.