Solved: Re: Employee working days calculation - Qlik Community

koushik_btech20 · ‎2015-08-07

Hello Everyone,

I have an issue regarding employee working days calculation between a date range. First of all I have data where I am getting project and employee wise project start date and project end date and I have also a Holiday master data. I need Employee wise working days figure. So the working days calculation logic is,

Employee Working Days=

(ALLOCATION_END_DATE-ALLOCATION_START_DATE ) - (Saturday & Sunday Between ALLOCATION_END_DATE & ALLOCATION_START_DATE ) - (Holiday Date from HolidayMaster)

So anyone have any solution please share here. I am also attaching demo data.

Kushal_Chawda · ‎2015-08-07

Here is the script solution

Hollidays:

LOAD date(Date,'DD-MM-YYYY') as Date,

Holiday

FROM

(biff, embedded labels, table is [Market Holidays - 2015$]);

List:

LOAD Distinct Concat(chr(39)& num(Date) &chr(39),',') as HolliDay_List

Resident Hollidays;

LET vHolliDays = Peek('HolliDay_List');

DROP Table List;

Data:

LOAD Distinct *,

NetWorkDays(Allocation_Start,Allocation_End,$(vHolliDays)) as Working_Days;

LOAD *,date(Date#(ALLOCATION_START_DATE,'DD/MM/YYYY'),'DD-MM-YYYY') as Allocation_Start,

date(Date#(ALLOCATION_END_DATE,'DD/MM/YYYY'),'DD-MM-YYYY') as Allocation_End Inline [

PROJECTID, EMPLOYEEID, ALLOCATION_START_DATE, ALLOCATION_END_DATE

PROJ00001, EMP0001, 12/02/2015, 31/08/2015

PROJ00001, EMP0002, 10/01/2015, 15/08/2015

PROJ00001, EMP0003, 25/03/2015, 31/10/2015

PROJ00002, EMP0001, 10/09/2015, 14/11/2015

PROJ00002, EMP0002, 05/10/2015, 30/11/2015

PROJ00002, EMP0003, 23/11/2015, 31/12/2015 ];

Create Straigh Table

Dimesion - EMPLOYEEID

Expression =sum(Working_Days )

Create listbox of project see the values

View solution in original post

Kushal_Chawda · ‎2015-08-07

try

Networkdays(ALLOCATION_START_DATE ,ALLOCATION_END_DATE, 'Holidays dates')

Anonymous · ‎2015-08-07

you can use function networkdays

empldays=ntworkdays(ALLOCATION_START_DATE,ALLOCATION_END_DATE, masterholidaylist)

Kushal_Chawda · ‎2015-08-07

Please see the attached

koushik_btech20 · ‎2015-08-07

Networkdays is not working .I am attaching a test application. Please resolve the issue on that attached application because I am using Qlikview Personal Edition. !

koushik_btech20 · ‎2015-08-07

please resolve the problem on my application what I have attached earlier. Your application is not opening because I am using Qlikview Personal Edition.

Kushal_Chawda · ‎2015-08-07

I think your date format in excel file is not proper. I have already attached the application

Kushal_Chawda · ‎2015-08-07

Data:

LOAD *,date(Date#(ALLOCATION_START_DATE,'DD/MM/YYYY'),'DD-MM-YYYY') as Allocation_Start,

date(Date#(ALLOCATION_END_DATE,'DD/MM/YYYY'),'DD-MM-YYYY') as Allocation_End Inline [

PROJECTID, EMPLOYEEID, ALLOCATION_START_DATE, ALLOCATION_END_DATE

PROJ00001, EMP0001, 12/02/2015, 31/08/2015

PROJ00001, EMP0002, 10/01/2015, 15/08/2015

PROJ00001, EMP0003, 25/03/2015, 31/10/2015

PROJ00002, EMP0001, 10/09/2015, 14/11/2015

PROJ00002, EMP0002, 05/10/2015, 30/11/2015

PROJ00002, EMP0003, 23/11/2015, 31/12/2015 ];

Hollidays:

LOAD date(Date,'DD-MM-YYYY') as Date,

Holiday

FROM

Holday_masters.xls

(biff, embedded labels, table is [Market Holidays - 2015$]);

koushik_btech20 · ‎2015-08-07

But using your formula I am getting wrong result,

Suppose for EMP00001 and PROJECT0001 ,

Start Date -> 12/02/2015 and End Date 31/08/2015

Duration is 201 days(including last day)

Saturday and Sunday = 29*2 =58

and No of Holiday is 32 between 12/02/2015 to 31/08/2015

So, The Employee Working days= 201-58-32=111 Days

You are not taking the holidays between 12/02/2015 to 31/08/2015.

And I need only Employee wise working days. So in Staright table when I am adding EMPLOYEEID as dimension and your formula in expression part nothing is showing, but when I am selecting a project I am getting a result. If you done the calculation inside the script part may it will be more helpful.

Kushal_Chawda · ‎2015-08-07

This?