NetworkDays Holiday List Aggregated by Employee

Anonymous — Thu, 12 Jan 2017 11:26:50 GMT

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

Re: NetworkDays Holiday List Aggregated by Employee

Gysbert_Wassenaar — Thu, 12 Jan 2017 17:02:05 GMT

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;

topic NetworkDays Holiday List Aggregated by Employee in QlikView

NetworkDays Holiday List Aggregated by Employee

Re: NetworkDays Holiday List Aggregated by Employee