Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
simongoodman
Creator
Creator

Working hours calc between dates

I would like to calculate working hours between two dates  based on a  created order datetimestamp and a completion datetimestamp.

I have looked at the various discussions and have an idea how to calculate a single fixed start work time and end work time for all working days.

There are two main issues.

1-Any out of hours created orders (before the start or after end of the working day) are assigned to the start of the next working day. Eg if an order is created Monday 20 pm the amended datetime is Tuesday 8pm. If the completion date&time is Tuesday 2pm the working hour difference is 6 hours.

2-Next how could I calculate working hours where say Monday to Friday working day starts 8am and finishes 18pm and Saturday starts 9pm and ends 14pm.

Thank you for any assistance

Simon

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Have a look at my first reply to the thread that Manish linked to.

You can create a solution without using the Networkdays() function and add different start and end times with some added logic, something along these lines:

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

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/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

6/24/16 05:00 PM, 6/27/16 09:00 AM

6/24/16 07:00 PM, 6/27/16 09:00 AM

];

TMP:

LOAD ID, Date,

  if(IterNo=1, rangemin(rangemax(frac(DT1),If(Weekday(Date)<5,maketime(8),MakeTime(9))),If(WeekDay(Date)<5,maketime(18),Maketime(14))), If(Weekday(Date)<5,maketime(8),Maketime(9))) as Start,

  if(daystart(DT1)+IterNo-1=daystart(DT2), rangemax(If(Weekday(Date)<5,maketime(8),maketime(9)),rangemin(frac(DT2),If(Weekday(Date)<5,maketime(18),Maketime(14)))),If(Weekday(Date)<5,Maketime(18),Maketime(14))) as End;

LOAD  ID,

daystart(DT1)+iterno()-1 as Date,

DT1,DT2,iterno() as IterNo

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP  where WeekDay(Date)<6 and not match(Date,$(vHol))  group by ID;

drop table TMP;

View solution in original post

7 Replies
sunny_talwar

Have you checked this link out? Business Working Hours Calculation

simongoodman
Creator
Creator
Author

Hi Sunny

I believe this link discussion looks to exclude Saturday and uses the NetWorkDays which is Monday to Friday. Also there is a single work start and end time.

MK_QSL
MVP
MVP

Hi Simon,

Provide some sample data..  I would work on it during weekends.

I have created similar application on IT ticketing system for my company.

Kindly provide sample data along with expected output.

MK_QSL
MVP
MVP

Or you can use this... similar approach...

Re: Calculate hours between two Date/Time strings

swuehl
MVP
MVP

Have a look at my first reply to the thread that Manish linked to.

You can create a solution without using the Networkdays() function and add different start and end times with some added logic, something along these lines:

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

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/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

6/24/16 05:00 PM, 6/27/16 09:00 AM

6/24/16 07:00 PM, 6/27/16 09:00 AM

];

TMP:

LOAD ID, Date,

  if(IterNo=1, rangemin(rangemax(frac(DT1),If(Weekday(Date)<5,maketime(8),MakeTime(9))),If(WeekDay(Date)<5,maketime(18),Maketime(14))), If(Weekday(Date)<5,maketime(8),Maketime(9))) as Start,

  if(daystart(DT1)+IterNo-1=daystart(DT2), rangemax(If(Weekday(Date)<5,maketime(8),maketime(9)),rangemin(frac(DT2),If(Weekday(Date)<5,maketime(18),Maketime(14)))),If(Weekday(Date)<5,Maketime(18),Maketime(14))) as End;

LOAD  ID,

daystart(DT1)+iterno()-1 as Date,

DT1,DT2,iterno() as IterNo

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP  where WeekDay(Date)<6 and not match(Date,$(vHol))  group by ID;

drop table TMP;

simongoodman
Creator
Creator
Author

Thank you all for your assistance. I believe Swuehl's approach will work, I testing it on live data.

vinayr67
Contributor II
Contributor II

Thanks for this solution.

Is it possible to provide some commentary on your script as I need to change some parameters (working times, etc.)?

Thanks.