Hi everyone, I'm having trouble wrapping my heading around using a multi-row formula. I have a list of truck moves that come in to pick up an item and go out to deliver the item and I need to calculate the average time of trips.
Data(Alteryx file attached)
For my multi-row formula I need to go to a specific In event, check all the events that happen later to find an Out event where the same truck has the same item. This is the main focus but there are a couple things in the data:
- There are anomalies where there can be more than one In or Out event for the same trip
- If there is more than one In event - Take the earliest In event as the start time
- If there is more than one Out event - Take the latest Out event as the end time
The problem I see is that I have sort the entire dataset(300+ records) by time ascending to find the following event but I need to check all events after instead of only Row+1/Row+2. Couple things I'm thinking of doing
- Sort by truck and time so that the events for each truck are next to each other to use Row+1
