topic Re: working hours and overtime in QlikView

working hours and overtime

Hakim-A — Sat, 16 Nov 2024 20:02:35 GMT

Hello Qlik community,

I am working on the calculation of hours spent on a document including office hours, week end and bank holidays.

I've found this very interesting dicussion

https://community.qlik.com/t5/QlikView-Documents/Business-Working-Hours-Calculation/ta-p/1477884/show-comments/true

and implemented the solution but it seems the formula is not working if we only have out of office hours.

Let's set this example

Office hours 9-14 15-19 every day except week ends and holidays

If we have a document started at 08:23:00 and finished at 08:39:00 then we expect to have

overtime 00:16:00

officetime 00:00:00

total time 00:00:16

the calculation shows 00:43:00 --> very strange

Other example :

document starts on 03/09/2019 20:00:00 and finished at 04/09/2019 17:00:00

We expect

overtime 13:00:00 (8pm to 9am)

office time 07:00:00 (9am to 2pm then 3pm to 5pm)

total time 20:00:00

the formula shows another result

I've created a small app so you can see the results and expected results.

I would need your help to find why it is not displaying the expected results.

In advance thanks for your contributions.

Re: working hours and overtime

Hakim-A — Fri, 20 Sep 2019 09:25:42 GMT

[URL=https://www.casimages.com/i/190920112546696063.jpg.html][IMG]https://nsa40.casimages.com/img/2019/09/20/190920112546696063.jpg[/IMG][/URL]

Re: working hours and overtime

StarinieriG — Fri, 20 Sep 2019 10:06:38 GMT

Hi,

the problem is here:

rangesum(if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)) ,
// round(
rangesum(if(frac(START_TIME)<maketime($(vStartHour)),maketime($(vStartHour))-frac(START_TIME),0),
if(frac(END_TIME)>maketime($(vEndHour)),frac(END_TIME)-maketime($(vEndHour)),0))

// ,0.01)
))

If you try to comment this part, it seems to work well. Let me know

Re: working hours and overtime

Hakim-A — Fri, 20 Sep 2019 10:18:38 GMT

Hello,

It works for normal hours - I've changed the script accordingly but there are still an issue

Let's take this example now (document G in my example)

Overtime should be 13:50:29 it shows 0...

example A overtime should be 00:16:07 it shows 0

example B overtime should be 00:04:44 it shows 0

thank you !

Re: working hours and overtime

StarinieriG — Fri, 20 Sep 2019 13:07:08 GMT

Try to see file attached.

I've tried to manage all these cases, but I'm not sure that the expression will cover all possible case.

If you'll find a new exception you have to add a new if (let me know if you'll need help).

Re: working hours and overtime

Hakim-A — Fri, 20 Sep 2019 13:21:23 GMT

Indeed - I think I did not get the whole understanding of the script and need to work on that in order to catch all exceptions (someone can also work during a bank holiday or a sunday...)

What if we have this case ?

What would be the if expression ?

I, 02/01/2019 14:28:34, 02/01/2019 15:38:34

Thank you very much !

Re: working hours and overtime

Brett_Bleess — Tue, 01 Oct 2019 15:24:22 GMT

Duplicate posts, see the following post for solution:

https://community.qlik.com/t5/New-to-QlikView/Business-hours-amp-overtime/m-p/1626164#M380992

Regards,
Brett