Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have following values
from_date 2020-01-01 12:00:00.000
to_date 2021-12-31 12:40:00.000
How do I get difference between these 2 date time stamp as 40
I can get difference but I want them in number as 40. Not 00:40
so I can sum the times as 40 + ...
Interval gives me difference but again in date timestamp format. not just the number 40.
I used SQL query
isnull(convert(datetime, from_date), '8:00') as start_time,
isnull(convert(datetime, to_date), '18:00') as end_time
Then in Qlik script I used
Time(Interval(end_time-start_time),'mm') as Total --- This gives me timestamp again. Not in Number
I used below expression in script to convert into mins with as is numbers.
First I extracted just the time using
Time(from_date) as From_TimeOnly,
Time(to_date) as To_TimeOnly,
Then to get only numbers
Interval#(To_TimeOnly, 'H:mm:ss TT') * 1440 - Interval#(From_TimeOnly, 'H:mm:ss TT') * 1440
Thanks for your help!
Num#(Interval(end_time-start_time),'mm'))
or
(end_time-start_time) * 1440
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
Tried, but they give me difference for date range times.
from_date 2020-01-01 12:00:00.000
to_date 2021-12-31 12:40:00.000
Date range above is from 01/01 to 12/31.. so it is giving diff of all the time between this range. But I just need 12:00 - 12:40.
I got it by using Time(Interval end_time- start_time),'mm').
In list box it looks like number but when you export it to excel or sum the values, it is giving incorrect answer.
Ex: 40 & 24 is 65 but I get 05
I recommend reading up on the difference between Formatting and Interpretation functions. Here's a good article
I'll assume that your values are proper qlik timestamp values, that it, the number of days is the integer value and time is the fractional value. If you want to ignore the date portion and just subtract the time portions use frac() to get the time (fractional) portion.
Frac(end_time) - Frac(start_time)
If you want that in minutes multiply the result by 1440.
-Rob
I used below expression in script to convert into mins with as is numbers.
First I extracted just the time using
Time(from_date) as From_TimeOnly,
Time(to_date) as To_TimeOnly,
Then to get only numbers
Interval#(To_TimeOnly, 'H:mm:ss TT') * 1440 - Interval#(From_TimeOnly, 'H:mm:ss TT') * 1440
Thanks for your help!
Glad you got it to work. Just to give you more to chew on.
Time(from_date) as From_TimeOnly,
is improper. Time() does not extract the time. It formats the Datetime as a string time. That forces you to reinterpret the value with Interpret#() when you use the field. Instead if, you did
Time(Frac(from_date)) as From_TimeOnly,
then your expression could be:
(To_TimeOnly - From_TimeOnly) * 1440
This becomes important for example, if you try to use From_TimeOnly as a chart Dimension or sort the field. It would not work if you use just the Time() function.
-Rob