Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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