topic Re: WorkingHours excluding weekends and Consider working hours between 9 to 17:30 in QlikView

WorkingHours excluding weekends and Consider working hours between 9 to 17:30

vidyarani — Fri, 15 Nov 2024 23:15:51 GMT

Hi Guys,

I hope you can help me on this!
I am creating a working hour field in the front end which should exclude weekends and consider working hours between 09 to 17:30

Formula used is:

=Interval(

(RangeMin(Frac(EndDate), MakeTime(17,30,00))

- RangeMax(Frac(StartDate), MakeTime(09,00,00)))

+ (NetWorkDays(StartDate, EndDate-1) * MakeTime(08,30,00))

)

The above formula works perfectly fine if StartDate time and EndDate time falls within 09 to 17:30 but it fails when these times are outside working hours.

For instance:

StartDate:7/8/2020 17:41:00

EndDate:7/8/2020 18:03:49

Ideally as per the logic working hours for these dates should be 0 as they fall outside working hours but output I am getting is 23:49:00 which is wrong.

Note: I have gone through all the posts over community but nothing worked out and also I can't create these calculations in the script as StartDate and EndDate are from two different tables and they are connected via the fact table.

Can someone help me on this?

Thanks and Regards,

Vidyarani

Re: WorkingHours excluding weekends and Consider working hours between 9 to 17:30

edwin — Tue, 26 Jan 2021 00:59:19 GMT

maybe you can add an if statement if both start date and end dates are inside the valid time range. since the fields are in separate tables, you can add two fields:

if start date time is beyond 1730 then start date is invalid

if end date time is before 900 then end date is invalid

so in your expression's if statement you test if both indicator fields are VALID

you may need to test all possible scenarios

Re: WorkingHours excluding weekends and Consider working hours between 9 to 17:30

rubenmarin — Tue, 26 Jan 2021 07:45:32 GMT

Hi @vidyarani, what is doing is:

- The rangemin gets 17:30

- The rangeMax gets 17:41

- The result is -11 minutes, I'm not sure why its shown as 23:49.

To solve this you can ad an if at start to check this case, like: If(Floor(StartDate)=Floor(EndDate) and Frac(StartDate)>MakeTime(17,30,00), 0, `YourExpression])

Another posible solution would be add a RangeMin() to RangeMax() and viceversa, like:

- RangeMax(RangeMin(Frac(EndDate), MakeTime(17,30,00)), MakeTime(09,00,00))

- RangeMin(RangeMax(Frac(StartDate), MakeTime(09,00,00)), MakeTime(17,30,00))

Re: WorkingHours excluding weekends and Consider working hours between 9 to 17:30

vidyarani — Thu, 04 Feb 2021 14:51:39 GMT

Hi Ruben,

Thank you so much for the response. Really appreciate it!

It works perfectly fine.

is it possible to implement same scenario in Script?

It would be great if you can share me the script.

Thanks and Regards,

Vidyarani

Re: WorkingHours excluding weekends and Consider working hours between 9 to 17:30

rubenmarin — Fri, 05 Feb 2021 07:36:24 GMT

Hi @vidyarani, I think you can do exactly the same in script to have it in a new field. Can you try it?

Maybe you need to merge tables if StartDate and EndDate are in different tables