topic Re: Date Diffrence in Hours Mins And Sec in QlikView

Date Diffrence in Hours Mins And Sec

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a date format like

[Create Time Release] [Release Time])

2015-12-17 09:52:24 AM 2015-12-17 09:58:22 AM

I used following script to find the diffrence in HH:MM:SS, But its not working for me.

Interval(

rangesum(

NetWorkDays([Create Time Release] +1,[Release Time]-1) * MakeTime(9) // 8 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays([Release Time],[Release Time]),Rangemin(rangemax(frac([Release Time]),maketime(7,30)),maketime(16,30))-Rangemax(rangemin(frac([Release Time]),maketime(7,30)),maketime(7,30)),0) // working hours last day

,if(NetWorkDays([Create Time Release] ,[Create Time Release] ),Rangemin(rangemax(frac([Create Time Release] ),maketime(16,30)),maketime(16,30))-Rangemax(rangemin(frac([Create Time Release] ),maketime(16,30)),maketime(7,30)),0) // working first day

,if(NetWorkDays([Create Time Release] ,[Create Time Release] ) and floor([Create Time Release] )=floor([Release Time]),-MakeTime(7,30)) // correct for first equals last day

)

as DurationRelease,

working hours 7:30 to 4:30 and used TimestampFormat= 'YYYY-MM-DD hh:mm:ss[.fff] TT';

Thanks,

Re: Date Diffrence in Hours Mins And Sec

PrashantSangle — Wed, 08 Jun 2016 05:57:34 GMT

Hi,

Interval(YourExpression, format)

please check below thread for detail

http://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/FormattingFunctions/Interval.htm?q=interval‌

Regards

Re: Date Diffrence in Hours Mins And Sec

sujeetsingh — Wed, 08 Jun 2016 05:59:35 GMT

Interval is the solution for your need.

Re: Date Diffrence in Hours Mins And Sec

avinashelite — Wed, 08 Jun 2016 06:06:23 GMT

interval(expression [ , format-code ])

The interval function formats the expression as a time interval according to the string given as a format-code. If the format code is omitted, the time format set in the operating system is used. Intervals may be formatted as a time, as days or as a combination of days, hours, minutes, seconds and fractions of seconds.

You are using two arguments, so QV tries to interprete your second argument as format code (which must be a format code string).

If you want to get the difference in days, you need to use a subtraction of your counts (if counting is correct here)

Re: Date Diffrence in Hours Mins And Sec

Anonymous — Wed, 08 Jun 2016 06:10:31 GMT

Please don't mark as a Correct Answer before my response on your reply.

Re: Date Diffrence in Hours Mins And Sec

Anonymous — Wed, 08 Jun 2016 06:12:59 GMT

Please help me swuehl‌

Re: Date Diffrence in Hours Mins And Sec

avinashelite — Wed, 08 Jun 2016 06:13:02 GMT

Don't worry only the community moderate has that rights others can't mark it , except you

Re: Date Diffrence in Hours Mins And Sec

Digvijay_Singh — Wed, 08 Jun 2016 06:13:25 GMT

No one can do that except you, AFAIK only creator of the discussion is allowed to mark correct answer

Re: Date Diffrence in Hours Mins And Sec

Anonymous — Wed, 08 Jun 2016 06:16:57 GMT

i am not marked as a Correct Answer

Re: Date Diffrence in Hours Mins And Sec

sasiparupudi1 — Wed, 08 Jun 2016 08:18:04 GMT

interval(timestamp(timestamp#('2015-12-17 09:58:22 AM','YYYY-MM-DD hh:mm:ss TT'))-timestamp(timestamp#('2015-12-17 09:52:24 AM','YYYY-MM-DD hh:mm:ss TT')),'hh:mm:ss')

hth

Sasi

Re: Date Diffrence in Hours Mins And Sec

Anil_Babu_Samineni — Wed, 08 Jun 2016 08:24:11 GMT

As follow Sasidhar

Might be like this

interval(timestamp(timestamp#('2015-12-17 09:58:22 AM','YYYY-MM-DD hh:mm:ss TT'))-timestamp(timestamp#('2015-12-17 09:52:24 AM','YYYY-MM-DD hh:mm:ss TT')),'YYYY-MM-DD hh:mm:ss TT')

Re: Date Diffrence in Hours Mins And Sec

jonathandienst — Wed, 08 Jun 2016 08:38:33 GMT

In the simplest form:

Interval([Release Time] - [Create Time Release], 'hh:mm:ss') as DurationRelease

if you have loaded the two fields as proper QV numeric date/time fields. If thay are still string values, then you will need

Interval(Timestamp#([Release Time], 'yyyy-MM-dd hh:mm:ss TT') - Timestamp#([Create Time Release], 'yyyy-MM-dd hh:mm:ss TT'), 'hh:mm:ss') as DurationRelease

Although I recommend that the Timestamp#()' s are in your load script so that you are storing date/time values and you can use the simple expression above.

Re: Date Diffrence in Hours Mins And Sec

jonathandienst — Wed, 08 Jun 2016 08:49:26 GMT

Your expression could be rendered:

Interval(

Rangesum(

NetWorkDays([Create Time Release] + 1, [Release Time] - 1) * 9/24,

If(NetWorkDays([Release Time], [Release Time]),

Rangemin(Rangemax(frac([Release Time]),maketime(7,30)),maketime(16,30)) -

Rangemax(Rangemin(frac([Release Time]),maketime(7,30)),maketime(7,30)), 0),

If(NetWorkDays([Create Time Release] ,[Create Time Release]),

Rangemin(Rangemax(frac([Create Time Release]),maketime(16,30)),maketime(16,30)) -

Rangemax(rangemin(frac([Create Time Release]),maketime(16,30)),maketime(7,30)), 0),

If(NetWorkDays([Create Time Release],[Create Time Release])

And floor([Create Time Release]) = floor([Release Time]),

-MakeTime(7,30))

)

'hh:mm:ss') as DurationRelease,

This is syntactically correct, but I am not sure if is logically correct as well.

Re: Date Diffrence in Hours Mins And Sec

ThornOfCrowns — Wed, 08 Jun 2016 09:26:09 GMT

If one of the answers is correct, please mark it as so.

Re: Date Diffrence in Hours Mins And Sec

swuehl — Wed, 08 Jun 2016 22:13:36 GMT

Do you still have issues with that expression (which I remember quite well, even if it's some years old)?

If so, it would be really helpful if you actually would describe your issues in details, giving some sample input records, returned results from the expression and the results you expect to get instead.

From what I can see for now, you have a 9 hour working day, so the correction for same start and end date should be

Interval(

Rangesum(

NetWorkDays([Create Time Release] + 1, [Release Time] - 1) * 9/24,

If(NetWorkDays([Release Time], [Release Time]),

Rangemin(Rangemax(frac([Release Time]),maketime(7,30)),maketime(16,30)) -

Rangemax(Rangemin(frac([Release Time]),maketime(7,30)),maketime(7,30)), 0),

If(NetWorkDays([Create Time Release] ,[Create Time Release]),

Rangemin(Rangemax(frac([Create Time Release]),maketime(16,30)),maketime(16,30)) -

Rangemax(rangemin(frac([Create Time Release]),maketime(16,30)),maketime(7,30)), 0),

If(NetWorkDays([Create Time Release],[Create Time Release])

And floor([Create Time Release]) = floor([Release Time]),

-MakeTime(9))

)

'hh:mm:ss') as DurationRelease,

If you issue is something else, please clarify (and also double check that your timestamps are actually read in correctly from your source:

Why don’t my dates work?

Get the Dates Right