Datediff based on another variable

Question & Answer
, , , ,
1

I’m trying to find the difference in time between a variable, but only when a different variable changes.

Here’s a sample from my dataset:

image
image508×898 34.5 KB

I want to know how much time the vehicle is moving each day, so I need to find the difference in timestamp - but only when there’s a change in the mileage variable. So hopefully I’ll be able to see that the vehicle was driven for 6mins between 09:24 and 09:30 (as the mileage has increased by one), and then I can sum all those minutes from a day together and know how often the vehicle is being driven

2

Hi @dom1

Try something like this

Time difference = ifelse(ifelse({Mileage} - lag({Mileage}, 1) > 0,1, 0) = 1, dateDiff(lag({Timestamp},1), {Timestamp}, ‘MI’),0)

sum(Time difference)

Aggregate Timestamp column to day for day wise minutes

3

Hi @dom1,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post or were you able to find a work around?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

4

Hi @dom1,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!