topic Re: Working hours only in QlikView

Working hours only

Wed, 31 Oct 2012 10:45:26 GMT

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

Re: Working hours only

swuehl — Wed, 31 Oct 2012 12:27:10 GMT

Please check

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

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

Hope this helps,

Stefan

Re: Working hours only

Wed, 31 Oct 2012 16:54:48 GMT

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

Re: Working hours only

Gysbert_Wassenaar — Wed, 31 Oct 2012 18:43:01 GMT

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.

Re: Working hours only

Thu, 01 Nov 2012 09:46:34 GMT

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

Re: Working hours only

swuehl — Thu, 01 Nov 2012 10:42:52 GMT

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

Re: Working hours only

Thu, 01 Nov 2012 10:48:57 GMT

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

Re: Working hours only

swuehl — Thu, 01 Nov 2012 11:18:37 GMT

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

Re: Working hours only

Thu, 01 Nov 2012 11:24:32 GMT

Hi Stefan,

where abouts in the script should this expression be put?

Thanks

Laura

Re: Working hours only

swuehl — Thu, 01 Nov 2012 11:33:18 GMT

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

];

Re: Working hours only

Thu, 01 Nov 2012 12:47:42 GMT

Hi Stefan,

Thanks for your help it has been really useful. I have got the expression working in the chart. However i have noticed it has dropped records where the start and end time are both at a weekend. i would expect it to bring back a 0. Also it has dropped records where a case is still open and only has a start date. i would just want it to return a blank in the table.

Any ideas?

Thanks

Laura

Re: Working hours only

Thu, 01 Nov 2012 12:47:54 GMT

Hi Stefan,

Any ideas?

Thanks

Laura

Re: Working hours only

swuehl — Thu, 01 Nov 2012 14:01:21 GMT

Regarding the dropped records: The expression will return zero, these lines will be hidden if 'suppress zero values' is checked in presentation tab of your chart. Uncheck this option.

Regarding the records with only start date: You can add an if() statement to check if your end date is set:

If( len(trim(case_closedate)),

.... // here comes the interval calculation

, ' ')

Re: Working hours only

Thu, 01 Nov 2012 14:32:23 GMT

Hi i have got this working now which is great thanks very much for your help.

My new issue is that for example

Start Date End Date Calculationof working hours

09.02.12 09:54:39 11.02.2011 09:14:49 19:20:09

This is the correct answer, however i want it to return that it took 1 day 9hrs and 20mins and 09 seconds.

Thanks

Laura

Re: Working hours only

swuehl — Thu, 01 Nov 2012 14:58:35 GMT

You could try something like

=floor(WorkingHours / maketime(10)) &' days '

& hour( WorkingHours-floor(WorkingHours,maketime(10)))&' hrs '

& Minute(WorkingHours-floor(WorkingHours,maketime(10))) &' mins and '

& second(WorkingHours-floor(WorkingHours,maketime(10))) &' seconds'

Re: Working hours only

Thu, 01 Nov 2012 15:09:04 GMT

Thats Great thanks very much for your help

Re: Working hours only

Thu, 01 Nov 2012 15:39:58 GMT

With regards to holidays i see you have called a holiday '41130' do dates have to go in this format?

or can i simply set holidays as '25/12/2012', 26/12/2012, '01/01/2013' etc

Re: Working hours only

swuehl — Thu, 01 Nov 2012 16:11:46 GMT

If that's your standard date format, it should work (41130 is just the numerical representation of 8/9/12).

Set DateFormat = 'DD/MM/YYYY';

Set vHol = '25/12/2012', '26/12/2012', '01/01/2013';

Note that I included each date into single quotes.

Check also

http://community.qlik.com/message/131162

on how to load the holidays from a table.

Re: Working hours only

Anonymous — Mon, 22 Dec 2014 01:41:46 GMT

Hi, What changes I would need if my total work hours are 20(1:00:00 AM to 22:00 PM)? I tried making the below changes but it didn't work:

NetWorkDays(Created+1,[ACK Date]-1) * MakeTime(20)
// 20 hours per workday, for all day inbetween the period, excluding bounderies
,if(NetWorkDays([ACK Date],[ACK Date]),Rangemin(rangemax(frac([ACK Date]),maketime(1)),maketime(22))-Rangemax(rangemin(frac([ACK Date]),maketime(1)),maketime(1)),0)
// working hours last day
,if(NetWorkDays(Created,Created),Rangemin(rangemax(frac(Created),maketime(22)),maketime(22))-Rangemax(rangemin(frac(Created),maketime(22)),maketime(1)),0) // working hours first day
,if(NetWorkDays(Created,Created) and floor(Created)=floor([ACK Date]),-MakeTime(20))
// correct for first equals last day
)
)
as RspHrs

Please advise. The 2 dates we use are Ack date(DT2) and Created(DT1)

Re: Working hours only

ajayvermaida — Mon, 10 Feb 2020 05:36:26 GMT

@swuehl

Hi Kush,

can you please help me to calculating the time diff between two dates based on given points

1- First I have to calculate the time weekdays in between 7 Am to 11 PM excluding sat and sun . am using below script for this but prob is that when both the dates are weekend then its also calculate the time diff .

if(not WildMatch(IssueStatus,'Pending') , (Interval((RangeMin(frac(IssueResolutionDate), MakeTime(23))- RangeMax(frac(IssueCreateDate), MakeTime(7)))+
( NetWorkDays(IssueCreateDate, IssueResolutionDate-1) * MakeTime(16)) ))) as TotalTime,

2- Second I have to calculate the time diff only Weekend Sat or Sun in between 10 Am to 4 PM . also if any dates is weekend and other one is weekday then it calculate the time based on diff time difference .

3- if any ticket open in the last month and closed in next month then it calculate the time as point 1 except if it comes on weekdays .

Please help me to find the correct result