topic Re: Bank holiday Exclusion in calculation in QlikView

Bank holiday Exclusion in calculation

mugdhaojha — Wed, 30 Sep 2015 04:47:43 GMT

Hi,

I have a requirement where I need to calculate the Resolution timing i.e Date Finish - Date Start.

From this calculation I need to exclude the bank holidays and weekends.

I know I need to use NetworkDays function to exclude the holidays but I am confused as to where to apply this?

Should I use it in the script while loading the Date Finish and Date Start and then use the Set analysis on the chart?

Any idea how I can implement this?

Re: Bank holiday Exclusion in calculation

datanibbler — Wed, 30 Sep 2015 06:57:10 GMT

Hi,

afaIk, Networkdays() will exclude the weekends, but not bank holidays.

For these - well, what I do is, I have a master_calendar (as a qvs file to INCLUDE) which I use in every one of my apps, and I have a list of all the bank holidays of one year which is fed into that calendar.

I imagine if you have such a list and you have some kind of date in your table, you could also join the list (with a LEFT JOIN, the main table being the primary one) and see where you have a match - those should be the bank holidays.

=> Then you can calculate a flag_field like "workday/ holiday" (or simply 1 and 0) based on that.

HTH

P.S.: This is the third post by me - in a row - that goes into moderation before being published. There must be something wrong with the admins' random generator or something ...

Re: Bank holiday Exclusion in calculation

jonathandienst — Wed, 30 Sep 2015 07:05:46 GMT

You can run a code snippet like this to populate a variable with a list of holidays

This uses the website timeanddate.com, but you could use an Excel file source or database. It also covers several years, so if you only need the current year, you don't need the For loop.

//----------------------------------------------------------------------------------------------------

Set zPriorYears = 5; // Number of prior years to include in calendar

Set zFutureYears = 3; // Number of future years to include in calendar

Let zToday = Today(1); // Date parameter (for unit testing, use other dates here)

//----------------------------------------------------------------------------------------------------

// Get holiday dates from timanddate.com

//----------------------------------------------------------------------------------------------------

Let zMinYear = Year(YearStart(AddYears(zToday - 5, -zPriorYears)));

Let zMaxYear = Year(YearEnd(AddYears(zToday, zFutureYears)));

For zi = zMinYear to zMaxYear

tmpHolidays:

LOAD Date(Date#(Date & ' ' & $(zi), 'MMM D YYYY')) As Date

FROM [http://www.timeanddate.com/holidays/uk/$(zi)]

(html, codepage is 1252, embedded labels, table is @1)

Where [Holiday type] = 'Public Holiday';

;

Next

tmpConcatHolidays:

LOAD chr(39) & Concat(Date, chr(39) & ',' & chr(39)) & chr(39) As HolidayList

Resident tmpHolidays;

Let vHolidayList = Peek('HolidayList');

DROP Tables tmpHolidays, tmpConcatHolidays;

Now you have a variable vHolidayList that contains the list of holidays. Use this in networkday() like this:

=NetWorkDays(start, finish, $(vHolidayList))

Re: Bank holiday Exclusion in calculation

jonathandienst — Wed, 30 Sep 2015 07:07:51 GMT