Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mohdhaniff
Creator
Creator

Net Total Working Time exclude Holiday

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;

2 Replies
Digvijay_Singh

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;

sasiparupudi1
Master III
Master III

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;