Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Calculating Holidays in script

mov
Esteemed Contributor III

Calculating Holidays in script

Every time I see a discussion which mentions holidays, there is a recommendation to keep holiday list in an external file or hardcoded using load inline.

Both these methods are valid.  QlikView is very good in loading data from multiple data sources, including files.  But there are drawbacks:
- External files have to be maintained, that is updated for the upcoming years.  Also they have to be placed into the appropriate directories for reload to work.
- Hardcoded maps has to be maintained as well.

I recommend the approach that utilizes internal QlikView functions.  As soon as we know the rules for the holidays, we can calculate all we need in the script.  Most holidays fall into one of the next groups:
- Fixed date holidays, for example New Year is always January 1st.  There is complication (in US but not in all countries as far as I know) that if a holiday of this type falls on Saturday, it is celebrated a day earlier on Friday, and if a holiday falls on Sunday, it is moved to the following Monday.
- Floating date holidays, for example Labor Day which is 1st Monday of September.
There are holidays that are not based on the common calendar.  I’m keeping them out of this consideration here.  Once used as load inline.  Feel free to comment on this.

See the attached document.  Script parts:
1. Master Calendar.  Four our purpose we need only Date and Year, but I’m adding a few more fields for the front end exercise.
2. Fixed Date Holidays.  The rule is included here to move holidays out of the weekend.
3. Floating holidays.  They can be certainly in the same loop as the fixed holidays, but I kept them separately when created first time, and left it this way for convenience.
4. The final part – variable that contains all holidays as comma-separated values.  This way it can be used in networkdays() and similar functions.
5. If desired, we can create two additional fields – Holiday, which is a text field containing Holiday name, and a flag IsHoliday (binary Y/N or 1/0).
6. As usual, cleanup at the end.

Feel free to comment and improve, and let me know if there are bugs.

Michael Solomovich

Tags (2)
Attachments
Comments
balkumarchandel
Valued Contributor II

Nicely explained.. Thanks msolomov

Not applicable

I agree.  One of the best Holiday apps I've found.  Thanks for sharing.

rajkumarb
Contributor II

Very Helpful App, Thank You

jebamalai
Valued Contributor

Very Good one

jenmclean
New Contributor II

How would I add Easter?

Version history
Revision #:
1 of 1
Last update:
‎01-24-2014 01:02 PM
Updated by:
mov