topic Re: Employee working days calculation in QlikView

Employee working days calculation

koushik_btech20 — Fri, 07 Aug 2015 12:25:25 GMT

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.

Re: Employee working days calculation

Kushal_Chawda — Fri, 07 Aug 2015 12:28:58 GMT

try

Networkdays(ALLOCATION_START_DATE ,ALLOCATION_END_DATE, 'Holidays dates')

Re: Employee working days calculation

Anonymous — Fri, 07 Aug 2015 12:30:33 GMT

you can use function networkdays

empldays=ntworkdays(ALLOCATION_START_DATE,ALLOCATION_END_DATE, masterholidaylist)

Re: Employee working days calculation

Kushal_Chawda — Fri, 07 Aug 2015 12:43:36 GMT

Please see the attached

Re: Employee working days calculation

koushik_btech20 — Fri, 07 Aug 2015 12:45:23 GMT

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. !

Re: Employee working days calculation

koushik_btech20 — Fri, 07 Aug 2015 12:50:26 GMT

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

Re: Employee working days calculation

Kushal_Chawda — Fri, 07 Aug 2015 13:00:52 GMT

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

Re: Employee working days calculation

Kushal_Chawda — Fri, 07 Aug 2015 13:02:31 GMT

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$]);

Re: Employee working days calculation

koushik_btech20 — Fri, 07 Aug 2015 13:29:18 GMT

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.

Re: Employee working days calculation

Kushal_Chawda — Fri, 07 Aug 2015 13:40:52 GMT

This?

Re: Employee working days calculation

koushik_btech20 — Fri, 07 Aug 2015 13:56:22 GMT

Please post your script here and also post what expression you did in your UI.

Re: Employee working days calculation

Kushal_Chawda — Fri, 07 Aug 2015 16:13:56 GMT

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

D:\Users\kchawda\Downloads\Holday_masters.xls

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

Create Variable vHollidays= Concat(chr(39)& Date &chr(39),',') in front end

Create Straigh Table

Dimesion - EMPLOYEEID

Expression =sum(aggr(sum(NetWorkDays(Allocation_Start,Allocation_End,$(vHollidays))), EMPLOYEEID) )

Create listbox of project see the values

Re: Employee working days calculation

Kushal_Chawda — Fri, 07 Aug 2015 18:52:41 GMT

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