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.