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

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