Forum Discussion
Calculate Route Stop Based on Distance/Duration
- 4 years ago
rsimpson318
Hello agian
I had some free time to do work on it. Here is a sample file folr your reference https://we.tl/t-WjszOERm4s
My proposed solution involves adding calculated columns:Start Time = VAR CurrentDistance = Routes[Distance] VAR LocationTable = CALCULATETABLE ( Routes, ALLEXCEPT ( Routes, Routes[Latitude], Routes[Longitude] ) ) RETURN IF ( CurrentDistance = 0 && COUNTROWS ( LocationTable ) > 1, MINX ( LocationTable, Routes[Date/Time] ) )End Time = VAR CurrentDistance = Routes[Distance] VAR LocationTable = CALCULATETABLE ( Routes, ALLEXCEPT ( Routes, Routes[Latitude], Routes[Longitude] ) ) RETURN IF ( CurrentDistance = 0 && COUNTROWS ( LocationTable ) > 1, MAXX ( LocationTable, Routes[Date/Time] ) )Stop = VAR StopNumber = RANKX ( FILTER ( Routes, NOT ISBLANK ( Routes[Start Time] ) ), Routes[Start Time],, ASC, Dense ) RETURN IF ( NOT ISBLANK ( Routes[Start Time] ), "Stop - " & StopNumber )Then the measure would be
Stop Duration = VAR Duration = DATEDIFF ( MAX ( Routes[Start Time] ), MAX ( Routes[End Time] ), SECOND ) RETURN QUOTIENT ( Duration, 60 ) & "Min and " & MOD ( Duration, 60 ) & " Sec"One last step would be to filter the table in order to remove the blank row
The report looks like this
I hope this satisfies your requirement.
rsimpson318
Here you go https://we.tl/t-71qnHty3mz
Good afternoon. Thank you for your help with this. I had a chance to test the solution and it seems to still be a bit off.
It seems to calculate the Start and Stop time if the distance traveled is equal to zero. In my table, the distance can fluctuate ever so slightly. In these instances, it would need to consider the start/stop when the vehicle moves less than .05 miles over an extended period.
I also have multiple vehicles and multiple dates. So you might have one vehicle that has 3 stops on the first day, and 4 on the second, and then another vehicle that also has a few stops one day and so on.
| Operator | Latitude | Longitude | Date/Time | TimeReceived | Distance | Duration | Stop |
| Driver 1 | Lat1 | Long1 | 4/29/2022 7:22 | 4/29/2022 7:22 | 0 | 0:00:20 | Stop - 1 |
| Driver 1 | Lat1 | Long1 | 4/29/2022 7:23 | 4/29/2022 7:23 | 0 | 0:00:20 | Stop - 1 |
| Driver 1 | Lat1 | Long1 | 4/29/2022 7:23 | 4/29/2022 7:23 | 0 | 0:00:20 | Stop - 1 |
| Driver 1 | Lat1 | Long1 | 4/29/2022 7:23 | 4/29/2022 7:23 | 0 | 0:00:20 | Stop - 1 |
| Driver 1 | Lat1 | Long1 | 4/29/2022 7:24 | 4/29/2022 7:24 | 0 | 0:00:20 | Stop - 1 |
| Driver 1 | Lat1 | Long1 | 4/29/2022 7:24 | 4/29/2022 7:24 | 0 | 0:00:20 | Stop - 1 |
| Driver 1 | Lat1 | Long1 | 4/29/2022 7:24 | 4/29/2022 7:24 | 0 | 0:00:20 | Stop - 1 |
| Driver 1 | Lat1 | Long1 | 4/29/2022 7:25 | 4/29/2022 7:25 | 0 | 0:00:20 | Stop - 1 |
| Driver 1 | Lat1 | Long1 | 4/29/2022 7:25 | 4/29/2022 7:25 | 0 | 0:00:20 | Stop - 1 |
| Driver 1 | Lat1 | Long1 | 4/29/2022 7:25 | 4/29/2022 7:25 | 0 | 0:00:20 | Stop - 1 |
| Driver 1 | Lat2 | Long2 | 4/29/2022 7:26 | 4/29/2022 7:26 | 0.01 | 0:00:21 | Stop - 1 |
| Driver 1 | Lat3 | Long3 | 4/29/2022 7:26 | 4/29/2022 7:26 | 0.06 | 0:00:20 | Stop - 1 |
| Driver 1 | Lat4 | Long4 | 4/29/2022 7:26 | 4/29/2022 7:26 | 0.05 | 0:00:19 | Stop - 1 |
| Driver 1 | Lat5 | Long5 | 4/29/2022 7:27 | 4/29/2022 7:27 | 0 | 0:00:20 | Stop - 1 |
| Driver 1 | Lat6 | Long6 | 4/29/2022 7:27 | 4/29/2022 7:27 | 0 | 0:00:21 | Stop - 1 |
| Driver 1 | Lat7 | Long7 | 4/29/2022 7:27 | 4/29/2022 7:27 | 0.14 | 0:00:20 | |
| Driver 1 | Lat8 | Long8 | 4/29/2022 7:28 | 4/29/2022 7:28 | 0.18 | 0:00:20 | |
| Driver 1 | Lat9 | Long9 | 4/29/2022 7:28 | 4/29/2022 7:28 | 0.17 | 0:00:20 | |
| Driver 1 | Lat10 | Long10 | 4/29/2022 7:28 | 4/29/2022 7:28 | 0.24 | 0:00:20 | |
| Driver 1 | Lat11 | Long11 | 4/29/2022 7:29 | 4/29/2022 7:29 | 0.11 | 0:00:19 | |
| Driver 1 | Lat12 | Long12 | 4/29/2022 7:29 | 4/29/2022 7:29 | 0.16 | 0:00:20 | |
| Driver 1 | Lat13 | Long13 | 4/29/2022 7:29 | 4/29/2022 7:29 | 0.04 | 0:00:20 | Stop - 2 |
| Driver 1 | Lat14 | Long14 | 4/29/2022 7:30 | 4/29/2022 7:30 | 0 | 0:00:20 | Stop - 2 |
| Driver 1 | Lat15 | Long15 | 4/29/2022 7:30 | 4/29/2022 7:30 | 0.03 | 0:00:20 | Stop - 2 |
| Driver 1 | Lat16 | Long16 | 4/29/2022 7:30 | 4/29/2022 7:30 | 0.01 | 0:00:21 | Stop - 2 |
| Driver 1 | Lat17 | Long17 | 4/29/2022 7:31 | 4/29/2022 7:31 | 0 | 0:00:19 | Stop - 2 |
| Driver 1 | Lat18 | Long18 | 4/29/2022 7:31 | 4/29/2022 7:31 | 0 | 0:00:20 | Stop - 2 |
| Driver 1 | Lat19 | Long19 | 4/29/2022 7:31 | 4/29/2022 7:31 | 0 | 0:00:20 | Stop - 2 |
| Driver 1 | Lat20 | Long20 | 4/29/2022 7:32 | 4/29/2022 7:32 | 0.01 | 0:00:21 | Stop - 2 |
| Driver 1 | Lat21 | Long21 | 4/29/2022 7:32 | 4/29/2022 7:32 | 0.5 | 0:00:20 | Stop - 2 |
| Driver 2 | Lat30 | Long30 | 4/29/2022 7:35 | 4/29/2022 7:35 | 0.08 | 0:00:20 | |
| Driver 2 | Lat32 | Long32 | 4/29/2022 7:36 | 4/29/2022 7:36 | 0.17 | 0:00:19 | |
| Driver 2 | Lat33 | Long33 | 4/29/2022 7:36 | 4/29/2022 7:36 | 0.05 | 0:00:21 | |
| Driver 2 | Lat34 | Long34 | 4/29/2022 7:36 | 4/29/2022 7:36 | 0.01 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:37 | 4/29/2022 7:37 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:37 | 4/29/2022 7:37 | 0 | 0:00:19 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:37 | 4/29/2022 7:37 | 0 | 0:00:21 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:38 | 4/29/2022 7:38 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:38 | 4/29/2022 7:38 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:38 | 4/29/2022 7:38 | 0 | 0:00:19 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:39 | 4/29/2022 7:39 | 0 | 0:00:21 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:39 | 4/29/2022 7:39 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:39 | 4/29/2022 7:39 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:40 | 4/29/2022 7:40 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:40 | 4/29/2022 7:40 | 0 | 0:00:19 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:40 | 4/29/2022 7:40 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:41 | 4/29/2022 7:41 | 0 | 0:00:21 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:41 | 4/29/2022 7:41 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:41 | 4/29/2022 7:41 | 0 | 0:00:19 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:42 | 4/29/2022 7:42 | 0 | 0:00:21 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:42 | 4/29/2022 7:42 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:42 | 4/29/2022 7:42 | 0 | 0:00:19 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:43 | 4/29/2022 7:43 | 0 | 0:00:21 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:43 | 4/29/2022 7:43 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:43 | 4/29/2022 7:43 | 0 | 0:00:19 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:44 | 4/29/2022 7:44 | 0 | 0:00:21 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:44 | 4/29/2022 7:44 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:44 | 4/29/2022 7:44 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:45 | 4/29/2022 7:45 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:45 | 4/29/2022 7:45 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:45 | 4/29/2022 7:45 | 0 | 0:00:20 | Stop - 1 |
| Driver 2 | Lat35 | Long35 | 4/29/2022 7:46 | 4/29/2022 7:46 | 0.16 | 0:00:20 |
- tamerj14 years agoCommunity Champion
rsimpson318
Actually while writing the code, I was trying to figure out the criteria but that was not clear so I assumed and waited for your feedback on the proposed solution.When you say "it would need to consider the start/stop when the vehicle moves less than .05 miles over an extended period." please define "extended period."