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.
Good morning and sorry for the late response. I was out on leave and didn't have a chance to download the file and the link seems to have expired. Would you mind reposting it?
Thank you.
rsimpson318
Here you go https://we.tl/t-71qnHty3mz
- rsimpson3184 years agoFrequent Visitor
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 ago
Community 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."