Business hours & 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.

[Josh_Berg_Support]

I don't have an answer for you as to why your QVW isn't working as you would like it to, however, I noticed this thread is now 10 days old and there haven't been any responses.  I would highly recommend changing the title of your thread to something like "Assistance with Qlikview Formula" or "Assistance with Load Script", that might get more attention than your current title.

Thanks,
Josh

Qlik Support

[rubenmarin · Accepted solution]

Hi, sorry but I don0t have time to post a full solution, just a few comments... Round to 0.01 will round to the most nearest time counting intervals of 14 minutes and 24 seconds, if you want to round to the nearest second try with "Round([Expression], 1/86400)" -> 86400 is the number of seconds in a day.

The A case has both times in the same day, before start hour, so you need to get the difference between both values, like:

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

Mainly the steps for calculation could be: Look if both dates are in the same day, if they are, look if both times are before start hour or after ending hour, in that case the overtime is the difference between this two values.

In other cases look differences between start and end hour, and add full times for intermediate days. 

[Brett_Bleess]

Did Ruben's post help you get things working as you wished?  If so, please be sure to use the Accept as Solution button on his post to give him credit and let others know this helped.  If you did something else, please consider posting that for others and mark that as the solution.  If you are still working upon things, leave an update on where you stand for us.

Regards,
Brett

[Hakim-A]

Hello,

Thanks for your replies. Indeed I had to write some script in order to get all conditions fulfilled.

It is a bit tricky because you can have several configurations of time but I did it.

Thanks

[SrPaz]

Dear,

could you please share your approach with the community, it'll be really appreciated.

Best regards.