Network days less Public Holidays

[#1]

Hi, Quick one!

I have a inbut box with the following constraint "NetWorkDays(vProposedStartDate,vProposedCompleteDate) "

This perfectly gives me the working days between to dates I have as variables, however I need public holidays subtracted from that number....so I brought in a list of public holidays (just their dates) and now I need to subtract it from the above constraint.

Please could someone tell me how to do this.

Sort of like this: --its just that this doesn't work. =NetWorkDays(vProposedStartDate,vProposedCompleteDate) - [PublicHolidays]

[#2]

=NetWorkDays(vPropsedStartDate,vProposedCompleteDate) - count({YOURdatefield = ">=$(vPropsedStartDate)<=$(vProposedCompleteDate)"}PublicHolidays)

This could work. If you attached a sample of your qvw that woudl help greatly

[#3]

Or just: =NetWorkDays(vProposedStartDate,vProposedCompleteDate) - count([PublicHolidays])

[Colin-Albert]

The networkdays function allows you to include a list of holidays to exclude from the calculation.

networkdays (start:date, end_date {, holiday})

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

Examples:

networkdays ('2007-02-19', '2007-03-01') returns 9  
networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8 

[Clever_Anjos]

Networkdays accepts a third parameter who holds a field that contains dates that are holidays

=NetWorkDays(vProposedStartDate,vProposedCompleteDate,[PublicHolidays])

[Colin-Albert]

for your data use

=NetWorkDays(vProposedStartDate, vProposedCompleteDate, vHolidayList)

[#7]

Thank you for the speedy response

Sorry, Maybe I should have added more detail.

the 2 Calendar variables I am using to obtain Network Days are input variables with a min of 01/01/2014 and a max of 01/01/2017

Hence I have loaded a file with the public holiday dates between that period.

so this subtracts the count of dates I have in the table. (which isn't correct) NetWorkDays(vProposedStartDate,vProposedCompleteDate) - count([PublicHolidays])

[Colin-Albert]

The network days function requires the list of holidays to be a comma separated list of dates. You can use LOAD CONCAT to create the comma separated list.

[Clever_Anjos · Accepted solution]

as Colin Albert suggested:

Holidays:

LOAD * INLINE [

    Holiday

    01/01/2014

    08/01/2014

    10/02/2014

];

load

    concat(chr(39) & Holiday & chr(39),',') as list

Resident Holidays;

LET ListOfHolydays = Peek('list');

and use =NetWorkDays('05/01/2014','10/01/2014',$(ListOfHolydays))