Working hours only

[#1]

Hi All,

I have to 2 timestamp fields Start Date and Closed Date. I want to calculate the time it takes from start date to close date.

i only want to include times between 8am and 6 pm and not including saturdays and sundays.

for example.

Start date is 30/10/2012 15:00:00 and Closed Date is 31/10/2012 10:00:00 i would expect the calculated to bring back 5 hours as the cycle time.

Can anyone help me work out how to do this calculation in Qlikview?

Thanks

Laura

[swuehl]

Please check

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

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

Hope this helps,

Stefan

[#3]

Hi Stefan,

i was hoping you could help me. i now have a working calculation to calculation working hours between two dates however a start date may be on a weekend. so i want to saying if it is on a weekend start the calculation from the start of the working day on a monday.

if the start date and close date are both on a weekend then the calculation should return a zero or if the end date is on a weekend count the end date is it was at close of business on a friday.

can you help with my issue. as currently the workinghours cal returns a minus number when the start or end date is on a weekend

thanks

laura

[Gysbert_Wassenaar]

Use lastworkingday(MyDate,1) to get the first next workday. If MyDate is a saturday or sunday it will return the date of the next monday, otherwise it will return MyDate. Likewise firstworkingday(MyDate) will get the friday before if MyDate is a saturday or sunday and otherwise return MyDate.

[#5]

Hi Gysbert,

thanks for resonding,

i have copied my script which works for calculating working hours.

Where would i put the firstworkingday(mydate,1) into the script?

//The sollution is as follows:

//$1 WorkDayStart. Format 9, 9.5, 21
//$2 WorkDayEnd.Format 9, 9.5, 21
//$3 TimeStampStart. Format DateTime
//$4 TimeStampEnd. Format DateTime

Set fxCalcWorkHours = (($2-$1)*NetWorkDays($3,$4))  -(24*(  FMOD($3,1)  -($1/24)  +($2/24)  -FMOD($4,1) ));

ODBC CONNECT TO xxx ;

Orders:
Load Workflow_ID,
     Customer_Name,
     case_created_date as Start_Date,
     case_close_date as close_date,
     $(fxCalcWorkHours(9,18,case_created_date, case_close_date)) as Case_WorkHoursSpent,
     etc .....

Thanks

Laura

[swuehl]

Hi Laura,

any reason why you don't try the solution I posted with the first link above? This should handle start and end of your interval even in case of weekend / holidays.

Regards,

Stefan

[#7]

HI Stefan,

i am really struggling to understand the logic, as i am not the best at Qlikview,

is it possible you can explain it too me. the solution i have posted above works but if it is outside office hours or at a weekend then the hours return a negative number.

What i am looking for is the following.

i have 2 dates Case_created_date and case_close_date

Working hours are 8am to 6pm.

If a case is opened outside office hours it will start the calculation from the next working day at 8am. If the case is closed outside working hours it will stop the calculation at the last working day at 6pm.

For example

31-10-2012 19:00:00       01-11-2012 10:00:00 i would expect the calculation to return 2 hours.

26-10-2012  14:00:00     28-10-2012 06:00:00 i would expect the calculation to return 4 hours.

Can you help me work this out?

Thanks

Laura

[swuehl · Accepted solution]

Hi Laura,

no problem, but which specific part of the expression do you have problems with?

This is the expression:

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(10)  

// 10 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)),Rangemin(rangemax(frac(DT2),maketime(8)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0)

// working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(8)),0) // working hours first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(10))

// correct for first equals last day

)

)

DT1 and DT2 are the field names for the start resp. end timestamp.

$(vHol) is a variable that holds your holidays.

The magic number Maketime(10) in the first and last argument to rangesum is equal to the number of hours of a full working day (08:00 to 18:00 = 10 hours).

In the following statements, maketime(8) and maketime(18) are giving start and end time of the working day, while frac(DT1) and frac(DT2) are giving the actual start and end time of the task.

So above expression should just work fine (maybe remove the holiday argument in the networkdays function, if not needed and replace DT1 and DT2 with your field names.

Regards,

Stefan

[#9]

Hi Stefan,

where abouts in the script should this expression be put?

Thanks

Laura

[swuehl]

Laura,

you can use this expression as expression in a chart or as part of the load script.

I assume you can't open others qvw files, because I actually posted a sample file demonstrating all this in the referenced link 😉

Here it the script code with some sample mock-up data loaded inline and two fake holidays:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID,

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(10)          // 10 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)),Rangemin(rangemax(frac(DT2),maketime(8)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(8)),0) // working first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(10)) // correct for first equals last day

)

)

as WorkingHours

  INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/8/12 11:08 AM,8/8/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,    7/6/12 4:38 PM

6/29/12 4:45 PM,    7/6/12 4:19 PM  

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

8/3/12 06:30 PM, 8/6/12 09:00 AM

8/3/12 03:00 PM, 8/6/12 07:00 AM

];