I'm using Interval() to count the number of minutes or days between the same date field (Date1) within the same ID.
Date1 and Date2 have the following format:
Date(Date#(Date1, 'DD-MMM-YYYY hh:mm'),'DD-MM-YYYY hh:mm') as Date1
Date(Date#(Date2, 'DD-MMM-YYYY hh:mm'),'DD-MM-YYYY hh:mm') as Date2
The Interval expression are defined like this:
Interval(Date2 - Date1,'mm') as IntervalA
If(ID = Previous(ID), Interval(Date1 - Previous(Date1),'mm')) as IntervalB
The Interval function does not apply across rows whenever the calculation is not withing the same day. I would like to achieve the output in IntervalC.
Is there a way to still use Interval() and get the output in IntervalC? Any other methods are appreciated as well.
ID | Date1 | Date2 | IntervalA | IntervalB (current output) | IntervalC (desired output) |
---|
A | 01-01-2018 12:00 | 01-01-2018 12:00 | 00 | - | - |
A | 07-01-2018 08:00 | 07-01-2018 08:01 | 01 | - | 8400 |
A | 07-01-2018 08:01 | 07-01-2018 08:02 | 01 | 01 | 01 |
A | 07-01-2018 08:02 | 07-01-2018 08:02 | 00 | 01 | 01 |
A | 07-01-2018 08:05 | 10-01-2018 08:05 | 4320 | 03 | 03 |
A | 10-01-2018 09:00 | 10-01-2018 09:10 | 10 | - | 4375 |
B | 16-01-2018 09:00 | 16-01-2018 09:01 | 01 | - | - |
B | 17-01-2018 09:00 | 17-01-2018 09:10 | 10 | - | 1440 |
Thx.