Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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')
)
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')
)
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!
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!