topic Re: Working hours in QlikView

Working hours

Wed, 26 Nov 2014 14:59:51 GMT

How do I calculate the Interval between two dates, and ignore AFTER working hours. In other words, only consider hours between 08:00 and 17:00.

Re: Working hours

rubenmarin — Wed, 26 Nov 2014 15:12:56 GMT

Hi Bradley, maybe there is a better solution but if it's fixed 9 work hours (and then 15 non-work hours) you can use:

=Interval((EndDateTimeField-StartDateTimeField) - Floor(EndDateTimeField-StartDateTimeField)*15/24)

Re: Working hours

Wed, 26 Nov 2014 15:16:52 GMT

Hi,

How are your Date fields ?

Can u share some sample data ?

Re: Working hours

agomes1971 — Wed, 26 Nov 2014 15:21:05 GMT

Hi,

maybe this could help...

Count of Working Days From Today

Regards

André Gomes

Re: Working hours

Wed, 26 Nov 2014 16:45:31 GMT

If you have an hour field in your data you can add a flag like this:

if(hour((Hour_Field-1)/86400)>=8 and hour((Hour_Field-1)/86400)<=17,1,0) as TimeWorking8to5,

Re: Working hours

Thu, 27 Nov 2014 12:56:19 GMT

Hi Ruben

Thanks for this. My result does not change, when I change the 15 to any other value. Do you know why? It also doesn't deduct the non-working hours.Please see the attached image. In the example The "Repair Due" is tomorrow morning, therefore it should only be a few hours.

Re: Working hours

rubenmarin — Thu, 27 Nov 2014 13:46:43 GMT

Hi Bradley, that yellow row seems ok, the difference between "27/11/2014 14:53:56" and "28/11/2014 11:23:00" is "20:29:04"

In the expression "15" is the non-working hours... Maybe I don't understand the issue, can you explain?

Re: Working hours

Thu, 27 Nov 2014 13:52:47 GMT

HI Ruben

Yes it is 20:29:04. But the non-working hours should still come off that, Because it's the next day. So it should actually be round about 5 hours only. And the expression as you gave it, does not give me 5. It gives me 20.

I hope that makes sense.

My expression : =Interval(([Original SLA2]-Now()) - Floor([Original SLA2]-Now())*15/24)

Re: Working hours

rubenmarin — Thu, 27 Nov 2014 14:09:37 GMT

You're right, I see a lot of faults in my expression like if start time is 16:55 and end time is next day at 8:05 my expression will count the first day as if you can still work 15 hours.

I'm bit of busy today and can't elaborate, if I have free time I'll try to help you with the solution.

Re: Working hours

Thu, 27 Nov 2014 14:11:48 GMT

Okay Thank You

Re: Working hours

rubenmarin — Thu, 27 Nov 2014 14:45:49 GMT

Ok Bradley, there it is:

=If(Floor(END)=Floor(START), Interval(END-START), //For task finished in the same day

// If not finished in the same day

Interval(17/24 - Time('$(=Time(START))') // Hours to finish the first day

+ Time('$(=Time(END))') - 8/24 // Hours from start of end day to due time

+ ((Floor(END)- Floor(START)-1)*9/24) //Complete days between

)

Hope his works, I didn't tested well.