Forum Discussion

Castillo3CS's avatar
Castillo3CS
Frequent Visitor
1 year ago

Calculating time between rows

Hi Team,   We're having trouble calculating time elapsed for vehicles that are idling. Sensor data for each vehicle is being sent and we need to know how much time a vehicle (bus_id) has spent in i...
Need Help
Castillo3CS's avatar
Castillo3CS
Frequent Visitor
1 year ago

My apologies, just noticed the mistake. When I sent the sample data I sorted it by Bus, the original data file comes with the IDs mixed, we have over 100 buses and data for them can come at the same time but recorded in different rows.

 

It looks something like this:

bus1 - time - value

bus1 - time - value

bus2 - time - vaue

bus1 - time - value

bus3 - time - value

.....

 

Hopefully this makes sense.

lbendlin's avatar
lbendlin
Super User
1 year ago

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

  • Castillo3CS's avatar
    Castillo3CS
    Frequent Visitor
    1 year ago

    Here's the sampel data arranged to look like ou table.

     

    IDbus_idsensor_valuedate1time1DateExpected
    14618Bus_002699.759/25/20242:29:27 PM9/25/2024 2:29:27 PM0
    14619Bus_002700.259/25/20242:29:28 PM9/25/2024 2:29:28 PM1
    14620Bus_0027019/25/20242:29:28 PM9/25/2024 2:29:28 PM0
    14901Bus_003699.759/25/20242:31:58 PM9/25/2024 2:31:58 PM0
    14902Bus_003699.8759/25/20242:31:59 PM9/25/2024 2:31:59 PM1
    14903Bus_003700.1259/25/20242:31:59 PM9/25/2024 2:31:59 PM0
    14904Bus_003700.59/25/20242:32:03 PM9/25/2024 2:32:03 PM4
    14621Bus_002699.8759/25/20242:29:28 PM9/25/2024 2:29:28 PM0
    14622Bus_002699.259/25/20242:29:32 PM9/25/2024 2:29:32 PM4
    14634Bus_002700.1259/25/20242:29:36 PM9/25/2024 2:29:36 PM4
    14635Bus_002699.8759/25/20242:29:36 PM9/25/2024 2:29:36 PM0
    14931Bus_0037009/25/20242:32:13 PM9/25/2024 2:32:13 PM10
    14932Bus_003699.6259/25/20242:32:13 PM9/25/2024 2:32:13 PM0
    14636Bus_002699.6259/25/20242:29:36 PM9/25/2024 2:29:36 PM0
    14637Bus_002699.3759/25/20242:29:37 PM9/25/2024 2:29:37 PM1
    14936Bus_0037019/25/20242:32:15 PM9/25/2024 2:32:14 PM2
    14937Bus_003699.8759/25/20242:32:16 PM9/25/2024 2:32:15 PM1
    14938Bus_003700.59/25/20242:32:16 PM9/25/2024 2:32:15 PM0
    14638Bus_002700.759/25/20242:29:37 PM9/25/2024 2:29:37 PM1
    14639Bus_002700.3759/25/20242:29:37 PM9/25/2024 2:29:37 PM1
    14900Bus_002700.259/25/20242:31:58 PM9/25/2024 2:31:58 PM141
    14933Bus_003701.259/25/20242:32:14 PM9/25/2024 2:32:15 PM0
    14934Bus_003699.59/25/20242:32:15 PM9/25/2024 2:32:16 PM1
    14935Bus_003699.59/25/20242:32:15 PM9/25/2024 2:32:16 PM0
    14939Bus_003700.259/25/20242:32:16 PM9/25/2024 2:32:16 PM0
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      1 year ago

      Hi,

      I used these calculated column formulas

      Date and time = Data[date1]+Data[time1]
      Diff = if(ISBLANK(CALCULATE(MAX(Data[Date and time]),FILTER(Data,Data[bus_id]=EARLIER(Data[bus_id])&&Data[Date and time]<EARLIER(Data[Date and time])))),time(0,0,0),Data[Date and time]-CALCULATE(MAX(Data[Date and time]),FILTER(Data,Data[bus_id]=EARLIER(Data[bus_id])&&Data[Date and time]<EARLIER(Data[Date and time]))))

      Hope this helps.

       

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      1 year ago

      Castillo3CS 

       Let's only take a look at bus 2

      | ID    | bus_id  | sensor_value | date1     | time1      | Date                 | Expected |
| ----- | ------- | ------------ | --------- | ---------- | -------------------- | -------- |
| 14618 | Bus_002 | 699.75       | 9/25/2024 | 2:29:27 PM | 9/25/2024 2:29:27 PM | 0        |
| 14619 | Bus_002 | 700.25       | 9/25/2024 | 2:29:28 PM | 9/25/2024 2:29:28 PM | 1        |
| 14620 | Bus_002 | 701          | 9/25/2024 | 2:29:28 PM | 9/25/2024 2:29:28 PM | 0        |
| 14621 | Bus_002 | 699.875      | 9/25/2024 | 2:29:28 PM | 9/25/2024 2:29:28 PM | 0        |
| 14622 | Bus_002 | 699.25       | 9/25/2024 | 2:29:32 PM | 9/25/2024 2:29:32 PM | 4        |
| 14634 | Bus_002 | 700.125      | 9/25/2024 | 2:29:36 PM | 9/25/2024 2:29:36 PM | 4        |
| 14635 | Bus_002 | 699.875      | 9/25/2024 | 2:29:36 PM | 9/25/2024 2:29:36 PM | 0        |
| 14636 | Bus_002 | 699.625      | 9/25/2024 | 2:29:36 PM | 9/25/2024 2:29:36 PM | 0        |
| 14637 | Bus_002 | 699.375      | 9/25/2024 | 2:29:37 PM | 9/25/2024 2:29:37 PM | 1        |
| 14638 | Bus_002 | 700.75       | 9/25/2024 | 2:29:37 PM | 9/25/2024 2:29:37 PM | 1        |
| 14639 | Bus_002 | 700.375      | 9/25/2024 | 2:29:37 PM | 9/25/2024 2:29:37 PM | 1        |
| 14900 | Bus_002 | 700.25       | 9/25/2024 | 2:31:58 PM | 9/25/2024 2:31:58 PM | 141      |

      could you pls explain how we get the expected output? why the last one is 141?

       

      • Castillo3CS's avatar
        Castillo3CS
        Frequent Visitor
        1 year ago

        Hi ryan_mayu ,

         

        Sure, 141 is the difference in seconds between 2:29:37 and 2:31:58.