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.