Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
simongoodman
Contributor

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
MVP
MVP

Re: Working hours calc between dates

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

Re: Working hours calc between dates

Have you checked this link out? Business Working Hours Calculation

Highlighted
simongoodman
Contributor

Re: Working hours calc between dates

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.

MVP
MVP

Re: Working hours calc between dates

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.

MVP
MVP

Re: Working hours calc between dates

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

Re: Calculate hours between two Date/Time strings

MVP
MVP

Re: Working hours calc between dates

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

simongoodman
Contributor

Re: Working hours calc between dates

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

vinayr66
New Contributor II

Re: Working hours calc between dates

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.