NetworkDays Holiday List Aggregated by Employee

[Anonymous]

Hi,

I am looking for some assistance, I need to calculate the Working days by Employee excluding their Leave and Public Holidays. I have attempted this via the script below. This issue is, it is not excluding their leave from the NetWorkDays calculation (ActualDays). I am wondering if this is an aggregation issue as each Employee has a different list of holidays? If I paste a string in directly for one Employee it removes the holidays but using the HD field appears to do nothing?

Holidays:

LOAD

    Consultant,

    Concat(chr(39) & "Leave Bank Holiday Dates" &chr(39),',') as HD

FROM [lib://QVD_Test/Timesheet_HolidayDates.qvd]

(qvd)

Group By Consultant

;

WorkDates:

LOAD

    RTrim(Capitalize("Name of Resource")) as Consultant,

    "PO Start date",

    "PO End Date"

FROM [lib://PO Data/PO Source Template.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(WorkDates)

LOAD * Resident Holidays;

DROP Table Holidays;

Final:

LOAD

  Consultant,

  NetWorkDays([PO Start date],[PO End Date], HD) as ActualDays

Resident WorkDates;

DROP Table WorkDates;

Many thanks,

Nikki

[Gysbert_Wassenaar]

Yeah, HD is just one string and therefore one argument. It's not a comma separated list of arguments you can pass to the NetWorkDays function that way.

But you can calculate per employee a sum of his/her holiday days that are not weekend days. You can then later subtract this number from the other working days:

Holidays:

LOAD

    Consultant,

    Sum(NetWorkDays([Leave Bank Holiday Dates],[Leave Bank Holiday Dates])) as HD

FROM [lib://QVD_Test/Timesheet_HolidayDates.qvd]

(qvd)

Group By Consultant;

WorkDates:

LOAD

    RTrim(Capitalize("Name of Resource")) as Consultant,

    "PO Start date",

    "PO End Date"

FROM [lib://PO Data/PO Source Template.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(WorkDates)

LOAD * Resident Holidays;

DROP Table Holidays;

Final:

LOAD

  Consultant,

  NetWorkDays([PO Start date],[PO End Date]) - HD as ActualDays

Resident WorkDates;

DROP Table WorkDates;