Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Difference in minutes between two timestamps

I have separate date and time fields loading to a Qlik Sense application.

The date fields are formatted in Qlik to look like: mm/dd/yyyy

The time fields are formatted in Qlik to look like: hh:mm:ss AM/PM.

I am contenating the Date and Time fields and formatting them with the timestamp function.

The following expression seems to be giving me a working format:

timestamp#(Date(CALLDT) & ' ' & time(CALLTIME)) as CallInDateTime,

timestamp#(Date(NSDATE) & ' ' & time(NSTIME)) as NoShowDateTime

But when I try to subtract CallInDateTime from NoShowDateTime I get a result of -.20 and it should be 310 (minutes) when the CallInDateTime is 2/7/2020 4:00:00 PM and the NoShowDateTime is 2/7/2020 11:10:06 AM since there's five hours and ten minutes difference between those two timestamps.

Is there a better way to get a difference in minutes between two timestamps, or do I need to use a better expression to format my DateTime fields?

Thanks.

 

 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Labels (2)
1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Update:

I found another topic answer that works great.  Here's an expression based on the other topic answer:

IF(SchedDateTime >= NoShowDateTime,

Interval(SchedDateTime - NoShowDateTime,'hh:mm:ss'),TIME(Interval#('24:00:00','hh:mm:ss')-Interval(NoShowDateTime - SchedDateTime,'hh:mm:ss'),'hh:mm:ss')

)

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

View solution in original post

3 Replies
Highlighted
Creator III
Creator III

Update:

I found another topic answer that works great.  Here's an expression based on the other topic answer:

IF(SchedDateTime >= NoShowDateTime,

Interval(SchedDateTime - NoShowDateTime,'hh:mm:ss'),TIME(Interval#('24:00:00','hh:mm:ss')-Interval(NoShowDateTime - SchedDateTime,'hh:mm:ss'),'hh:mm:ss')

)

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

View solution in original post

Highlighted
Creator II
Creator II

You state that you subtracted CallInDateTime from NoShowDateTime (i.e., NoShowDateTime - CallInDateTime). In your example it would be: 2/7/2020 11:10:06 AM - 2/7/2020 4:00:00 PM which is a difference of -289.9 minutes, which is -0.20 day (289.9/60/24). This matches your result. 

Instead, if you would like to have the result in minutes as you described, the following formula will work:

(CallInDateTime - NoShowDateTime) * 1440

 

Hope it helps!

Highlighted
Creator III
Creator III

Thanks for your contribution; it helps explain the result of my initial attempt.  I ended up using the Interval expression as outlined above.

Thanks again!