Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Please check
http://community.qlik.com/message/252929#252929
http://community.qlik.com/message/272111#272111
Hope this helps,
Stefan
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.
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
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.
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?
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?
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;
Have you looked into the other solutions discussed not using the JOIN approach? They should return the number wanted for weekends.