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

Hi Anonymous ,

 

I'm still getting some very odd values for time difference, notice the 1125431 and the 1125280. If we do the math between the two dates there’s now way we’re getting those numbers.

 

I was going to try something with index columns but I'm in the middle of implementing it, so I’m not sure it’ll work.

 

Thanks a lot for the help.

lbendlin's avatar
lbendlin
Super User
1 year ago

Those are different buses.  Change the formula to return null instead.

  • 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