Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Network days less Public Holidays

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]

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Re: Network days less Public Holidays

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))

8 Replies
Not applicable

Re: Network days less Public Holidays

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

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

Not applicable

Re: Network days less Public Holidays

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

Re: Network days less Public Holidays

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 

Employee
Employee

Re: Network days less Public Holidays

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

=NetWorkDays(vProposedStartDate,vProposedCompleteDate,[PublicHolidays])



Re: Network days less Public Holidays

for your data use

=NetWorkDays(vProposedStartDate, vProposedCompleteDate, vHolidayList)

Not applicable

Re: Network days less Public Holidays

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])

Re: Network days less Public Holidays

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.

Highlighted
Employee
Employee

Re: Network days less Public Holidays

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))