Using Interval to count #minutes between rows in same date field

szeconku — Mon, 29 Oct 2018 11:10:32 GMT

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.

topic Using Interval to count #minutes between rows in same date field in App Development

Using Interval to count #minutes between rows in same date field