Calculate time difference af a row with its previous row.

[Aziz Arrashid]

Hi All

 

I need to calculate the difference of a row with its previous row in a date/time column. If the time difference is greater than 30 minutes, I need to flag the row.

Attached is the sample data. Please help.

Thanks

[Colin Gray 3]

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