Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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.

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Calculate Working Hour Interval Between Two Dates

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

8 Replies
MVP
MVP

Re: Calculate Working Hour Interval Between Two Dates

Not applicable

Re: Calculate Working Hour Interval Between Two Dates

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.

MVP
MVP

Re: Calculate Working Hour Interval Between Two Dates

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

Re: Calculate Working Hour Interval Between Two Dates

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

Re: Calculate Working Hour Interval Between Two Dates

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?

MVP
MVP

Re: Calculate Working Hour Interval Between Two Dates

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

Re: Calculate Working Hour Interval Between Two Dates

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;

MVP
MVP

Re: Calculate Working Hour Interval Between Two Dates

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

Community Browser