Discussion Board for collaboration on QlikView Scripting.
I have two date fields, I have to calculate the difference in hours. viz., open date and close date.
10/01/2016 08:20:00 12/01/2016 9:50:00
I used timestamp for both the columns and on the result also.
Eg: timestamp(Close_Date) - timestamp(Open_Date ) as Cycletime
Now i am getting the time in hours and minutes as below.
Eg: difference between abve dates is 49:30:00.
What I am expecting is like 49.50 hours(I have to calculate that 30 minutes into hours then add that to the 49 hours)
Thanks for any ideas.
Go to Solution.
timestamp(close date-opendate) as difference time
=round( (frac(timestamp(closedate))- frac(timestamp(opendate)))*24,0.01)
As far as I can see,
Round( (Close_Date - Open_Date)*24, 0.01) AS CycleTime
should be enough
Is the result is in minutes or hours.. I am getting wrong values??
negative values are getting when i use the above expression..?
Sweuhl , am I clear with my requirement ??
Could this work?
Add a variable Close_Date and Open_Date in script
(DayNumberOfYear(Close_Date) - DayNumberOfYear((Open_Date)))
And to get in hours:
Yes. CycleTime should be in hours, with a fraction part as requested (0.5 equals 30 mins):
SET TIMESTAMPFORMAT = 'DD/MM/YYYY hh:mm:ss';
Round( (Close_Date - Open_Date)*24, 0.01) AS CycleTime,
timestamp(Close_Date - Open_Date) as differencetime;
LOAD * INLINE [
10/01/2016 08:20:00, 12/01/2016 9:50:00
Can't see that the suggested expression you've chosen as correct answer delivers 49.50, mine expression does:
I don't understand how this could be a correct answer