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

Thanks for the promt reply. First time poster so I really appreciate the help.

 

Attached data and expected results. Hopefully I got it right this time.

 

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
14621Bus_002699.8759/25/20242:29:28 PM9/25/2024 2:29:28 PM1
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 PM1
14636Bus_002699.6259/25/20242:29:36 PM9/25/2024 2:29:36 PM1
14637Bus_002699.3759/25/20242:29:37 PM9/25/2024 2:29:37 PM1
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
14901Bus_003699.759/25/20242:31:58 PM9/25/2024 2:31:58 PM1
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 PM1
14904Bus_003700.59/25/20242:32:03 PM9/25/2024 2:32:03 PM4
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 PM1
14933Bus_003701.259/25/20242:32:14 PM9/25/2024 2:32:14 PM0
14934Bus_003699.59/25/20242:32:15 PM9/25/2024 2:32:15 PM1
14935Bus_003699.59/25/20242:32:15 PM9/25/2024 2:32:15 PM1
14936Bus_0037019/25/20242:32:15 PM9/25/2024 2:32:15 PM0
14937Bus_003699.8759/25/20242:32:16 PM9/25/2024 2:32:16 PM1
14938Bus_003700.59/25/20242:32:16 PM9/25/2024 2:32:16 PM1
14939Bus_003700.259/25/20242:32:16 PM9/25/2024 2:32:16 PM1
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, Castillo3CS 

 

I modified some of your formulas.

Next = MAXX(FILTER(Ralentis,
                       Ralentis[bus_id]=EARLIER(Ralentis[bus_id]) &&
                       Ralentis[Date]<EARLIER(Ralentis[Date])
               ),Ralentis[Date])
Column = IF([sensor_value]>=701, 0,IF(ISBLANK([Next]),
               ABS(DATEDIFF([Date],NOW(),SECOND)),
               ABS(DATEDIFF([Date],[Next],SECOND))
           )
       )

Please check, is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • 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.