QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for
Did you mean:
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
MVP

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:
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;
daystart(DT1)+iterno()-1 as Date,
DT1,DT2,iterno() as IterNo
Resident INPUT
while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)
ID,
interval(sum(End-Start)) as Duration
Resident TMP  where WeekDay(Date)<6 and not match(Date,\$(vHol))  group by ID;

drop table TMP;
```
7 Replies
MVP

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.

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.

MVP

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

Re: Calculate hours between two Date/Time strings

MVP

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:
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;
daystart(DT1)+iterno()-1 as Date,
DT1,DT2,iterno() as IterNo
Resident INPUT
while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)
ID,
interval(sum(End-Start)) as Duration
Resident TMP  where WeekDay(Date)<6 and not match(Date,\$(vHol))  group by ID;

drop table TMP;
```
Creator
Author

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

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.