# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for
Did you mean:
Contributor III

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

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

Labels (1)
• ### qlik

1 Solution

Accepted Solutions
MVP

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

4 Replies
Master

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

MVP

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

Contributor III
Author

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

MVP

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