topic Re: Interval with working days in QlikView

Interval with working days

Dayna — Tue, 07 Jun 2016 09:13:39 GMT

Dear All,

Hopefully this is a nice and simple one, but clearing it's not coming to me today!!

I have a calculation performed in the script to calculate the duration between two days including working time, which works perfect and provides me with an output in hours and minutes. I want to format this to show it in days, hours and minutes, but unfortunately, it shows the number of days (as in 24 hours) for this value rather than a working day-day (as in 8 hours).

So, an example set of data would be:

Date Start: 18/04/2016 - 14:17:42

Date End: 27/041/2016 - 12:18:40

Duration: 54:00:58

.... So far so good!!

In days, this would be 6 Days, 6 Hours 1 Minute....

... However, if I was to do Interval(54:00:58,'DD hh:mm:ss), I get 02 06:00:58 as a 'day' is 24 hours.

Can you advise on what I need to do to format this to get a working day in the result?

Many thanks for your assistance!

Kind Regards,

Dayna

Re: Interval with working days

sunny_talwar — Tue, 07 Jun 2016 09:15:17 GMT

Try this with a single D may be

Interval(54:00:58,'D hh:mm:ss)

What is your expectation here? How do you want to see it?

Re: Interval with working days

Dayna — Tue, 07 Jun 2016 09:17:09 GMT

Hi Sunny,

That gives the same value of 02 06:00:58.

Many thanks,

Dayna

Re: Interval with working days

sunny_talwar — Tue, 07 Jun 2016 09:18:49 GMT

Ya I figured that you are looking for something else, can you elaborate on how the output should look like?

Re: Interval with working days

Dayna — Tue, 07 Jun 2016 09:20:39 GMT

Hi Sunny,

It should look like '06 06:00:58', as the 54 hours in the original duration should be 54 hours over an 8 hour working day.

Kind Regards,

Dayna

Re: Interval with working days

sunny_talwar — Tue, 07 Jun 2016 09:34:37 GMT

May be not the best ways to do it, but seems to work:

=Floor(Interval#('54:00:08', 'h:mm:ss')/MakeTime(8)) & ' ' & Interval(Frac(Interval#('54:00:08', 'h:mm:ss')/MakeTime(8)) *(8/24) , 'h:mm:ss')

Re: Interval with working days

Tue, 07 Jun 2016 09:36:11 GMT

Hi Dayna,

floor((Num('54:00:58')/(1/3)))&' '&Time(((Num('54:00:58')/(1/3))-floor((Num('54:00:58')/(1/3))))*(1/3),'hh:mm:ss')

try this

-Joyson G

Re: Interval with working days

sunny_talwar — Tue, 07 Jun 2016 09:37:23 GMT

Or this to give it an true timefield interpretation:

=Interval#(Floor(Interval#('54:00:08', 'h:mm:ss')/MakeTime(8)) & ' ' & Interval(Frac(Interval#('54:00:08', 'h:mm:ss')/MakeTime(8)) *(8/24) , 'h:mm:ss'), 'D hh:mm:ss')

Re: Interval with working days

crusader_ — Tue, 07 Jun 2016 09:40:15 GMT

Similar idea but a bit simpler math...

=Interval#(floor(interval#('54:00:58', 'hh:mm:ss'))*3&' '&time(frac(interval#('54:00:58', 'hh:mm:ss')),'hh:mm:ss'), 'D hh:mm:ss')

/Andrei

Re: Interval with working days

crusader_ — Tue, 07 Jun 2016 09:43:00 GMT

more details:

*3 means = /(8/24) = /1/3 = *3

/Andrei

Re: Interval with working days

ThornOfCrowns — Tue, 07 Jun 2016 09:55:16 GMT

Based on previous replies, where Finish is your Finish date, Start is your Start date:

LOAD * INLINE [
Start, Finish
18/04/2016 14:17:42, 27/04/2016 12:18:40
];

=Interval#(Floor(Interval#(
Interval(

(RangeMin(frac(Finish), MakeTime(18))

- RangeMax(frac(Start), MakeTime(8)))

+ (NetWorkDays(Start, Finish-1) * MakeTime(8)) // Only 8 hours per whole day

)
, 'h:mm:ss')/MakeTime(8)) & ' ' &
Interval(Frac(Interval#(
Interval(

(RangeMin(frac(Finish), MakeTime(18))

- RangeMax(frac(Start), MakeTime(8)))

+ (NetWorkDays(Start, Finish-1) * MakeTime(8)) // Only 8 hours per whole day

)
, 'h:mm:ss')/MakeTime(8)) *(8/24) , 'h:mm:ss'), 'D hh:mm:ss')

Re: Interval with working days

Dayna — Tue, 07 Jun 2016 10:13:40 GMT

Wow, thanks for the many responses, many ways to get to the result... Thanks all, much appreciated!!