Qlik Community

QlikView Documents

QlikView documentation and resources.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.

How to remove weekend and holidays from your calculation

elie_issa
Creator II
Creator II

How to remove weekend and holidays from your calculation

Hi All,

A very important function to use to get the number of days and remove the weekends and holidays.

Syntax:

networkdays (start_date, end_date [, holiday])

Example :

networkdays ('19/12/2013', '07/01/2014', '25/12/2013', '26/12/2013')

Returns 12. This example takes the holiday 25/12/2013 to 26/12/2013 into account.

Reference : https://help.qlik.com/en-US/sense/3.0/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/networkd...

Comments
agni_gold
Specialist III
Specialist III

So we need to write all holidays dates in syntax?

0 Likes
elie_issa
Creator II
Creator II

yes it seems so based on the example found in the reference link above.

0 Likes
MK_QSL
MVP
MVP

Holidays:

Load * Inline

[

  HolidayDate

  01/01/2016

  05/01/2016

  11/01/2016

  12/01/2016

  03/06/2016

];

ConcatHolidays:

Load

  CHR(39) & Concat(HolidayDate,Chr(39)&','&Chr(39)) & CHR(39) as ConcatHolidays

Resident Holidays;

Drop Table Holidays;

Let vHolidays = Peek('ConcatHolidays',0,'ConcatHolidays');

Drop Table ConcatHolidays;

=========================================

Load all holidays from a database or excel file, concatenate them and create a variable.

Now you can use vHolidays variable inside networkdays function.

narband2778
Creator II
Creator II

// Calculating NetWorking Days (Excludidng Saturdays, Sundays and Public Holidays(UK))

Let vYearStart =NUM(YearStart(Today(),-1,4));

Let vYearEnd = NUM(YearEnd(Today()));

DATE:

LOAD

  Date($(vYearStart) + rowno() -1) as Date

AUTOGENERATE $(vYearEnd)- $(vYearStart)+1;

tmpHoliday:

Load * INLINE

[

  Holiday, HDate

  New Year's Day, 01/01/2013

  Good Friday, 29/03/2013

  Easter Monday, 01/04/2013

  Early May Bank Holiday, 06/05/2013

  Spring Bank Holiday, 27/05/2013

  Summer Bank Holiday, 26/08/2013

  Christmas Day, 25/12/2013

  Boxing Day, 26/12/2013

  New Year's Day, 01/01/2014

  Good Friday, 18/04/2014

  Easter Monday, 21/04/2014

  Early May Bank Holiday, 05/05/2014

  Spring Bank Holiday, 26/05/2014

  Summer Bank Holiday, 25/08/2014

  Christmas Day, 25/12/2014

  Boxing Day, 26/12/2014

  New Year's Day, 01/01/2015

  Good Friday, 03/04/2015

  Easter Monday, 06/04/2015

  Early May Bank Holiday, 04/05/2015

  Spring Bank Holiday, 25/05/2015

  Summer Bank Holiday, 31/08/2015

  Christmas Day, 25/12/2015

  Boxing Day, 28/12/2015

  New Year's Day, 01/01/2016

  Good Friday, 25/03/2016

  Easter Monday, 28/03/2016

  Early May Bank Holiday, 02/05/2016

  Spring Bank Holiday, 30/05/2016

  Summer Bank Holiday, 29/08/2016

  Christmas Day, 26/12/2016

  Boxing Day, 27/12/2016

];

tmpConcat:

LOAD concat(chr(39) & HDate & chr(39),',') as HolidayDates

RESIDENT tmpHoliday;

Let vPublicHolidays = fieldvalue('HolidayDates',1);

DROP TABLE tmpHoliday;

DROP TABLE tmpConcat;

0 Likes
TKendrick20
Partner
Partner

Kind of a bummer this formula doesn't work like the one in Excel where you can specify a column name in the Holiday parameter instead of having to use a CSV.

0 Likes
oknotsen
Master III
Master III

Soooo, nobody wrote a script yet that calculates the most common holidays each year?

The basic ones are easy; Christmas and New Year, but did anyone try their hands yet on a few of the others?

 

Edit:

... and after just looking into the math needed to calculate Easter, I totally understand why nobody might have bothered yet. I guess this will become another project some day.

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2016-07-15 01:45 AM
Updated by: