Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
szeconku
Contributor II
Contributor II

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

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.

IDDate1Date2IntervalAIntervalB (current output)IntervalC (desired output)
A01-01-2018 12:0001-01-2018 12:0000--
A07-01-2018 08:0007-01-2018 08:0101-8400
A07-01-2018 08:0107-01-2018 08:02010101
A07-01-2018 08:0207-01-2018 08:02000101
A07-01-2018 08:0510-01-2018 08:0543200303
A10-01-2018 09:0010-01-2018 09:1010-4375
B16-01-2018 09:0016-01-2018 09:0101--
B17-01-2018 09:0017-01-2018 09:1010-1440

Thx.

0 Replies