Employee working days calculation

[koushik_btech20]

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.

[koushik_btech20]

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

[Kushal_Chawda]

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

[Kushal_Chawda · Accepted solution]

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