Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- WorkingHours excluding weekends and Consider worki...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

vidyarani

Contributor III

2021-01-23
07:25 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

691 Views

1 Solution

Accepted Solutions

rubenmarin

MVP

2021-01-26
02:45 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

edwin

Master II

2021-01-25
07:59 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

637 Views

rubenmarin

MVP

2021-01-26
02:45 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

vidyarani

Contributor III

2021-02-04
09:51 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

580 Views

rubenmarin

MVP

2021-02-05
02:36 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

566 Views