working hours and overtime

[Hakim-A]

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/sho...

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.

[Hakim-A]

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

[StarinieriG]

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

[Hakim-A]

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 !

[StarinieriG]

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).

[Hakim-A]

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 !

[Brett_Bleess · Accepted solution]

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