topic Net Total Working Time exclude Holiday in QlikView

Net Total Working Time exclude Holiday

mohdhaniff — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am facing problem to count workingtimes from StartDate to EndDate where the office hour from Time_Start='08:45:00' to Time_End='17:45:00'. I already try using the solution provided on others discussion in this forum but still not resolved.

Hope I can get the correct way on how to compute the WorkTimes below: -

a. Period from StartDate to EndDate;

b. Exclude Holiday;

c. time range is actual time from StartDate to EndDate between 08:45:00 - 17:45:00;

1. if StartDate or EndDate is less than 08:45:00, the time to be counted as 08:45:00;

2. if StartDate or EndDate is more than 05:45:00, the time to be counted as 05:45:00;

3. else to use the actual time for StartDate or EndDate;

4. for the date between StartDate and EndDate to be used time as 08:45:00 till 05:45:00 (9hours) - exclude Public Holiday.

set Time_Start='08:45:00';
set Time_End='17:45:00';

set Holiday='1/2/2018','8/2/2018','16/2/2018','20/2/2018','23/2/2018','25/2/2018';

DATA:
NoConcatenate
LOAD Distinct * Inline [
No, StartDate, EndDate

01135002201201819829, 23/01/2018 21:27:50, 13/02/2018 11:17:19
01135060602201804002, 09/02/2018 08:48:07, 27/02/2018 11:46:22
01135060902201808420, 12/02/2018 15:26:59, 27/02/2018 15:58:09
01135062002201813660, 20/02/2018 14:08:53, 27/02/2018 18:09:04
01135300102201800812, 02/02/2018 16:07:52, 15/02/2018 11:18:08
01135300102201800831, 02/02/2018 12:02:57, 06/02/2018 16:09:11
01135300102201800984, 07/02/2018 11:01:28, 12/02/2018 15:14:39
01135300201201800708, 03/01/2018 09:31:40, 05/02/2018 14:37:38
01135300202201801268, 06/02/2018 09:30:26, 08/02/2018 11:02:32
01135300202201801574, 05/02/2018 13:44:51, 21/02/2018 12:43:22
01135300202201801893, 14/02/2018 16:10:05, 27/02/2018 15:00:34
01135300301201801775, 03/01/2018 17:41:04, 01/02/2018 06:16:22
01135300402201802237, 05/02/2018 12:28:28, 27/02/2018 16:24:08
01135300502201802386, 06/02/2018 07:32:07, 12/02/2018 12:29:02
01135300502201802422, 07/02/2018 17:53:49, 13/02/2018 20:01:14
01135300502201802472, 06/02/2018 11:51:19, 14/02/2018 12:13:09
01135300502201802507, 06/02/2018 12:07:23, 22/02/2018 12:57:31

];

Work:
LOAD Distinct
*,
NetWorkDays(date#(left(StartDate,10),'dd/MM/yyyy'),date#(left(EndDate,10),'dd/MM/yyyy'),$(Holiday)) as WorkDays ,
NetWorkDays(timestamp#(StartDate,'dd/MM/yyyy jhh:mm:ss'),timestamp#(EndDate,'dd/MM/yyyy hh:mm:ss'),$(Holiday)) as WorkTimes
Resident DATA;

DROP Tables DATA;

Re: Net Total Working Time exclude Holiday

Digvijay_Singh — Tue, 13 Mar 2018 14:05:06 GMT

Can you explain further on these conditions in relation to first row in your inline table? Also share the expected output.

1. if StartDate or EndDate is less than 08:45:00, the time to be counted as 08:45:00;

2. if StartDate or EndDate is more than 05:45:00, the time to be counted as 05:45:00;

Re: Net Total Working Time exclude Holiday

sasiparupudi1 — Tue, 13 Mar 2018 15:06:55 GMT

Try Like below.. You could apply/Change the rules on top of the fields

Let Time_Start=Num(Time#('08:45:00','hh:mm:ss'));

Let Time_End=Num(Time#('17:45:00','hh:mm:ss'));

set Holiday='1/2/2018','8/2/2018','16/2/2018','20/2/2018','23/2/2018','25/2/2018';

DATA:

Load

No,

IF(StartTime#<=$(Time_Start),Timestamp(StartDate#+$(Time_Start),'DD/MM/YYYY hh:mm:ss'),ActualStartDate) As StartDate,

IF(EndTime#>=$(Time_End),Timestamp(EndDate#+$(Time_End),'DD/MM/YYYY hh:mm:ss'),ActualEndDate) As EndDate,

StartDate#,

StartTime#,

EndDate#,

EndTime#,

ActualStartDate,

ActualEndDate;

LOAD

No,

Floor(Timestamp#(StartDate,'DD/MM/YYYY hh:mm:ss')) As StartDate#,

Frac(Timestamp#(StartDate,'DD/MM/YYYY hh:mm:ss')) As StartTime#,

Floor(Timestamp#(EndDate,'DD/MM/YYYY hh:mm:ss')) As EndDate#,

Frac(Timestamp#(EndDate,'DD/MM/YYYY hh:mm:ss')) As EndTime#,

TimeStamp(Timestamp#(StartDate,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') As ActualStartDate,

TimeStamp(Timestamp#(EndDate,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') As ActualEndDate

Inline [

No, StartDate, EndDate

01135002201201819829, 23/01/2018 21:27:50, 13/02/2018 11:17:19

01135060602201804002, 09/02/2018 08:48:07, 27/02/2018 11:46:22

01135060902201808420, 12/02/2018 15:26:59, 27/02/2018 15:58:09

01135062002201813660, 20/02/2018 14:08:53, 27/02/2018 16:09:04

01135300102201800812, 02/02/2018 16:07:52, 15/02/2018 11:18:08

01135300102201800831, 02/02/2018 12:02:57, 06/02/2018 16:09:11

01135300102201800984, 07/02/2018 11:01:28, 12/02/2018 15:14:39

01135300201201800708, 03/01/2018 09:31:40, 05/02/2018 14:37:38

01135300202201801268, 06/02/2018 09:30:26, 08/02/2018 11:02:32

01135300202201801574, 05/02/2018 13:44:51, 21/02/2018 12:43:22

01135300202201801893, 14/02/2018 16:10:05, 27/02/2018 15:00:34

01135300301201801775, 03/01/2018 17:41:04, 01/02/2018 15:16:22

01135300402201802237, 05/02/2018 12:28:28, 27/02/2018 16:24:08

01135300502201802386, 06/02/2018 07:32:07, 12/02/2018 12:29:02

01135300502201802422, 07/02/2018 17:53:49, 13/02/2018 17:01:14

01135300502201802472, 06/02/2018 11:51:19, 14/02/2018 12:13:09

01135300502201802507, 06/02/2018 12:07:23, 22/02/2018 12:57:31

];

Work:

LOAD Distinct

NetWorkDays(Floor(StartDate),Floor(EndDate),$(Holiday)) as WorkDays

//NetWorkDays(timestamp#(StartDate,'dd/MM/yyyy jhh:mm:ss'),timestamp#(EndDate,'dd/MM/yyyy hh:mm:ss'),$(Holiday)) as WorkTimes

Resident DATA;

DROP Tables DATA;