Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]
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))
=NetWorkDays(vPropsedStartDate,vProposedCompleteDate) - count({YOURdatefield = ">=$(vPropsedStartDate)<=$(vProposedCompleteDate)"}PublicHolidays)
This could work. If you attached a sample of your qvw that woudl help greatly
Or just: =NetWorkDays(vProposedStartDate,vProposedCompleteDate) - count([PublicHolidays])
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
Networkdays accepts a third parameter who holds a field that contains dates that are holidays
=NetWorkDays(vProposedStartDate,vProposedCompleteDate,[PublicHolidays])
for your data use
=NetWorkDays(vProposedStartDate, vProposedCompleteDate, vHolidayList)
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])
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.
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))