topic Re: Generic holiday mapping in QlikView

Generic holiday mapping

markp201 — Wed, 23 May 2018 12:41:47 GMT

!So, checking out the various holiday discussions, I noticed that holidays are nothing

more than a date pattern. For example, christmas is always the 25th so will always be one of the following

Mon Dec25,Christmas

Tue Dec25,Christmas

Wed Dec25,Christmas

Tbu Dec25,Christmas

Fri Dec25,Christmas

Mon Dec26,Christmas

Mon Dec27,Christmas

Note : last two are when the holiday falls on the weekend

Floating holidays have a slightly different pattern. In the US, memorial day is the last Monday in May

or put another way, memorial day occurs during the the last 7 days of May which means its one of the following

Mon May25,Memorial Day

Mon May26,Memorial Day

Mon May27,Memorial Day

Mon May28,Memorial Day

Mon May29,Memorial Day

Mon May30,Memorial Day

Mon May31,Memorial Day

There's no need to check the year because the pattern is independent of the year

When I create my master calendar, I simply use this mapping table for the six US holidays

MAPPING_TO_HOLIDAY:

MAPPING LOAD * INLINE [

DatePattern,Holiday

Mon Jan1,New Years

Tue Jan1,New Years

Wed Jan1,New Years

Tbu Jan1,New Years

Fri Jan1,New Years

Mon Jan2,New Years

Mon Jan3,New Years

Mon May25,Memorial Day

Mon May26,Memorial Day

Mon May27,Memorial Day

Mon May28,Memorial Day

Mon May29,Memorial Day

Mon May30,Memorial Day

Mon May31,Memorial Day

Mon Jul4,Independence Day

Tue Jul4,Independence Day

Wed Jul4,Independence Day

Tbu Jul4,Independence Day

Fri Jul4,Independence Day

Mon Jul5,Independence Day

Mon Jul6,Independence Day

Mon Sep1,Labor Day

Mon Sep2,Labor Day

Mon Sep3,Labor Day

Mon Sep4,Labor Day

Mon Sep5,Labor Day

Mon Sep6,Labor Day

Mon Sep7,Labor Day

Thu Nov22,Thanksgiving

Thu Nov23,Thanksgiving

Thu Nov24,Thanksgiving

Thu Nov25,Thanksgiving

Thu Nov26,Thanksgiving

Thu Nov27,Thanksgiving

Thu Nov28,Thanksgiving

Mon Dec25,Christmas

Tue Dec25,Christmas

Wed Dec25,Christmas

Tbu Dec25,Christmas

Fri Dec25,Christmas

Mon Dec26,Christmas

Mon Dec27,Christmas

]

;

and use the following in the master calendar load

APPLYMAP('MAPPING_TO_HOLIDAY',TEXT(DATE(tempdate,'WWW MMMD')),'N/A') AS Holiday

Note: update from OP. Need TEXT function because the mapping is string based.

Tried this with most of the US holidays and it works better than expected.

Easter is a bit of a challenge.

Appreciate any comments if I missed something. Happy Qliking

Attached a sample document based on Michael's post Calculating Holidays in script

Added feature:

- Observed vs Actual holiday

- Ability to add holidays for other countries

Enjoy

Re: Generic holiday mapping

marcus_sommer — Wed, 23 May 2018 13:05:30 GMT

The easter sunday could be calculated and various related holidays could then be generated with a simple offset of n-days of it. How it could be calculated and also be implemented (with a mapping too) could you find here: Master Calendar with movable holidays.

- Marcus

Re: Generic holiday mapping

markp201 — Thu, 24 May 2018 17:32:14 GMT

Still researching but I believe Easter is dependent on Christmas of the previous year.

Another advantage is setting up observed vs. actual holidays

Mon Jan1,New Years

Tue Jan1,New Years

Wed Jan1,New Years

Thu Jan1,New Years

Fri Jan1,New Years

Sat Jan1,New Years

Sun Jan1,New Years

Mon Jan3,New Years Observed

Mon Jan2,News Years Observed

Re: Generic holiday mapping

marcus_sommer — Thu, 24 May 2018 17:40:10 GMT

AFAIK it's not (directly) related to christmas else (if I remember correctly) it's the first sunday after the first full moon after the first day of the spring and therefore it changed with each year.

- Marcus

Re: Generic holiday mapping

Peter_Cammaert — Thu, 24 May 2018 22:13:39 GMT

And even then, there is a difference between the Western Christian calendar (Gregorian calendar) and the Eastern Christian Calendar (Julian calendar) which made Easter in Greece fall this year exactly one week after Easter in most other countries.

Re: Generic holiday mapping

marcus_sommer — Fri, 25 May 2018 05:02:41 GMT

Yes, Peter is right and there are various differences between the calendars. If you ever need more to this topic take a look here: Non-Gregorian calendars.

- Marcus