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?
Concat(chr(39) & "Leave Bank Holiday Dates" &chr(39),',') as HD
Group By Consultant
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);
LOAD * Resident Holidays;
DROP Table Holidays;
NetWorkDays([PO Start date],[PO End Date], HD) as ActualDays