Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculate Working Hour Interval Between Two Dates

Hi,

I am trying to calculate the working hour interval between two dates (close date and open date).

The requirement is to exclude Thursday and Friday (as weekends) from the duration and working hours should be

7.30 AM to 6.00 PM. The interval should be calculated duration only for business hours and therefore also excluding weekends.

I did calculate this in the script but the interval value seems to be more than a couple of hours against the desired value.

Could somebody please tell me where I'm going wrong?

Please see the attached files for a sample.

Regards & Thanks,

-Khaled.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Khaled,

first, please check the string format codes for date and time, MM is denoting month, not minute, so you must use something like

       timestamp(close_date,'MM/DD/YYYY hh:mm:ss') as close_date,

       timestamp(open_date,'MM/DD/YYYY hh:mm:ss') as open_date,

to get a proper time part (it's confusing to see the month as minutes in your sample app ;-).

Your time diiference is caused by your custom weekend (Thu & Fri) limitation, you need to use

...

where (WeekDay(Date)<>3 and WeekDay(Date)<>4) and not match(Date,$(vHol))

...

Please check the WeekDay() function, which is returning a number in the range from 0 (Mon) to 6 (Sun).

Using above changed where clause, I get 23:02:24 for your sample (the time worked on Sat is appr. 5, not 6 hours).

Hope this helps,

Stefan

View solution in original post

8 Replies
Not applicable
Author

Stefan,

Thank you for your response. I have already used yours posts as reference to get the solution, and it almost works fine. But I am a little perplexed to see that the interval is still not exactly as desired (or maybe I'm missing something here since there is difference of a few hours between the actual and the desired interval)

Since, you coined this solution, could you kindly take a look at the attached sample to see what's missing/wrong with the code?

Would appreciate your cooperation.

Thanks,

-Khaled.

swuehl
MVP
MVP

Khaled,

first, please check the string format codes for date and time, MM is denoting month, not minute, so you must use something like

       timestamp(close_date,'MM/DD/YYYY hh:mm:ss') as close_date,

       timestamp(open_date,'MM/DD/YYYY hh:mm:ss') as open_date,

to get a proper time part (it's confusing to see the month as minutes in your sample app ;-).

Your time diiference is caused by your custom weekend (Thu & Fri) limitation, you need to use

...

where (WeekDay(Date)<>3 and WeekDay(Date)<>4) and not match(Date,$(vHol))

...

Please check the WeekDay() function, which is returning a number in the range from 0 (Mon) to 6 (Sun).

Using above changed where clause, I get 23:02:24 for your sample (the time worked on Sat is appr. 5, not 6 hours).

Hope this helps,

Stefan

Not applicable
Author

Stefan,

Indeed, it's confusing and too silly I did that. Such a bloomer .

Thankyou for all the corrections

Many thanks again.....Stay awesome

Regards,

-Khaled.

Not applicable
Author

Question - did something similar, but now when the date is not a working day, the duration isn't being calculated at all. I'd rather that if the days are weekends that the duration simply say 00, but have a value. how do i accomplish this?

swuehl
MVP
MVP

Which of the discussed expressions are you using (if possible post a small sample file)?

Have you checked that you are not using a chart with 'suppress zero values' enabled in presentation tab?

Not applicable
Author

I essentially have the exact same setup as the original post. Bulk of relevant script below. However, because the left join has a where weekday(date)<5, if the date is a weekend, the left join results in a blank value for that row. So ultimately i would like to be able to modify this such that the working hours and days are set within the initial TMP table such that for day 5 & 6 working hourse are 00:00:00 - 00:00:00.

left join (jira_issue)

load %KEY_ISSUE

, priority_set_date

, priority_name

resident issue_priority ;

/// set variable for holidays

set vHol = '41459,41094';

TMP:

LOAD  %KEY_ISSUE,

daystart(priority_set_date)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(priority_set_date),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(priority_set_date)+iterno()-1=daystart(closed_datetime), rangemax(maketime(8),rangemin(frac(closed_datetime),maketime(18))),Maketime(18)) as End

Resident jira_issue

while daystart(closed_datetime) >= daystart(priority_set_date)+iterno()-1;

left join (jira_issue)

LOAD

%KEY_ISSUE,

interval(sum(End-Start), 'hh:mm:ss') as Priority_duration

Resident TMP  where WeekDay(Date)<5 and not match(Date,$(vHol))   group by %KEY_ISSUE;

drop table TMP;

drop table issue_priority;

swuehl
MVP
MVP

Have you looked into the other solutions discussed not using the JOIN approach? They should return the number wanted for weekends.