Jump to content
We've recently updated our Privacy Statement, available here. ×

Calculate time difference af a row with its previous row.


Aziz Arrashid

Recommended Posts

Hi there,

 

You can do this using an OVER statement with the Previous expression. Here is a calculated column which I think gives you what you need:

 

If(DateDiff("minute",Max([Date/Time]) Over (Previous([Date/Time])),[Date/Time])>30,True,False)

 

I am just returning True if is it greater than 30 minutes, false if not but you can change that to be whatever terminology you wish.

 

I actually would build something like this in 2 or 3 steps first just to check it is working. So to explain the expression above:

 

1) This expression: Max([Date/Time]) Over (Previous([Date/Time]) gets the previous datetime (note that OVER statements always need an aggregation, so I just use Max. You could put most things here and it won't affect anything such as Min, First etc. as we are only doing the aggregation on one row i.e. the previous row of data).

2) I then use the DateDiff expression to get the difference in minutes by comparing the result of the epxression above, to the current row's value for Date/Time

3) Finally, we wrap this all in an IF statement so we can do our 30 minute check

 

Hope this helps

 

Thanks

Colin

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...