topic Re: Calculate Working Hour Interval Between Two Dates in QlikView

Calculate Working Hour Interval Between Two Dates

Fri, 15 Feb 2013 14:08:37 GMT

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.

Re: Calculate Working Hour Interval Between Two Dates

swuehl — Fri, 15 Feb 2013 15:34:19 GMT

Please check

http://community.qlik.com/message/252929#252929

http://community.qlik.com/message/272111#272111

Hope this helps,

Stefan

Re: Calculate Working Hour Interval Between Two Dates

Fri, 15 Feb 2013 17:46:28 GMT

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.

Re: Calculate Working Hour Interval Between Two Dates

swuehl — Fri, 15 Feb 2013 18:37:24 GMT

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

Re: Calculate Working Hour Interval Between Two Dates

Fri, 15 Feb 2013 21:42:17 GMT

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.

Re: Calculate Working Hour Interval Between Two Dates

Wed, 10 Jul 2013 20:01:49 GMT

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?

Re: Calculate Working Hour Interval Between Two Dates

swuehl — Wed, 10 Jul 2013 22:12:05 GMT

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?

Re: Calculate Working Hour Interval Between Two Dates

Wed, 10 Jul 2013 22:25:35 GMT

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;

Re: Calculate Working Hour Interval Between Two Dates

swuehl — Wed, 10 Jul 2013 22:28:37 GMT

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