Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More

Master Calendar with movable holidays

MVP & Luminary
MVP & Luminary

Master Calendar with movable holidays

Beside the main-use of a master-calendar to create various date-clustering fields like week, month, quarter, year and similar to the date-values could be an extended use the calculation of working days. Of course you could within the GUI use  something like: count(distinct Date) or networkdays(Date1, Date2, OptionalHolidays) to calculate these values but they have some limitations in their functionalities and the usability. For example, networkdays() counts only days from Monday to Friday and a holiday-list over years is quickly cluttering and a count() approach will be probably need several conditions to get the expected results.

Therefore it is easier to transfer these task into the script - especially if your workingday-counting don't go from Monday to Friday and if you need to respect movable holidays like the Easter holidays.

The attached application creates a multi-level holiday-listing in an inline-table which then filled several mapping-tables which are then used for each date to determine is it a holiday or not and which factor will be applied. Beside fixed holidays like Christmas it's only necessary to calculate the Easter Sunday then most of the other Christian holidays are fixed related to this day. To calculate the Easter Sunday I use this expression (see update below):

let vOstern = round(num(date(makedate(year($(vStart) + $(i)), 4, day(minute(year($(vStart) + $(i)) / 38) / 2 + 55)), 'DD.MM.YYYY')) / 7) * 7 - 6;

let vDay = fabs(floor(if(floor((minute(year($(vStart) + $(i)) / 38) / 2 + 55) / 30) = 1, 31, 30) *
              
floor((minute(year($(vStart) + $(i)) / 38) / 2 + 55) / 30)) -

                                floor((minute(year($(vStart) + $(i)) / 38) / 2 + 55)));
let vOstern = round(makedate(year($(vStart) + $(i)), 4, $(vDay)
) / 7) * 7 - 6;

Within the GUI you could the simply use sum(Workingday) to get the amount of workingdays related to your selections and/or further conditions in your expressions. Further on a monthly level are the workingdays cumulated and compared with the max. number of workingdays in this month to get a forecast-factor. Also included are a WeekCounter and a MonthCounter to simplify year-overlapping calculations.

This calendar counts the days from Monday to Saturday and is related to german holidays and to a special week counting which excludes that the 01.01. could be belong to a week 53 or the 31.12. could be associated to week 1 - but I'm sure that you could adapt it to your needs. Further informations about master calendars could you find here: How to use - Master-Calendar and Date-Values

Update:

Unfortunately the first easter-expression here - which is an adoption from an excel-solution - didn't work always correctly. In some years (for example 2011) there is an offset from a week to the correct date.

The reason for it is a different handling of the day()-function between Excel and QlikView which I couldn't comprehend completely. Part of it is the different starting point of the calendar-counting (01.01.1900 in Excel and 31.12.1899 in QlikView) and that Excel has in error by the leap-year in 1900: https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-le.... But I think there are further differences how the day-functions are handled internally by both tools.

Nevertheless I found a solution by extending those day-calculation to a check if the round off raw-value / 30 is 1 or higher and which is here integrated in an extra variable. I assume this logic could be a bit more elegant but it was the easiest way my excel detail-checking to implement as it was without further optimizations. This was now checked for the years 2000 - 2025!

You are invited to give feedback and suggest improvements. Have fun!


Marcus Sommer

Labels (2)
Attachments
Comments
Partner
Partner

Very nice post, Marcus

It's easy to adapt to other countrie and is a good solution to the saturday problem of networkingdays()

Eduardo

0 Likes
Employee
Employee

Hallo Markus,

ich glaube, Deine Funktion zur Berechnung von Ostern funktioniert nicht immer.

Für das Jahr 2011 gibt sie den 17.4. als Ostersonntag zurük, Ostern war in diesem Jahr aber am 24.4.

Kannst Du das bitte noch einmal überprüfen?

Christian Nowak

MVP & Luminary
MVP & Luminary

Hallo Christian,

vielen Dank für Dein Feedback. Ich habe hierzu ein Update mit noch ein paar Anmerkungen zu den Hintergründen eingestellt.

Schöne Grüße

Marcus

0 Likes
Employee
Employee

Vielen Dank für die Anpassung!

Und wirklich sehr interessant, was die Ursache war. 😉

Ich habe mir mal die Mühe gemacht und die Berechnung nach 'Spencers Osterformel' (siehe wikipedia) im Skript umgesetzt. Das meine Ergänzug als copy + paste für Interessierte:

(kann auch als vorangehendes LOAD verwendet werden)

Eastern:

LOAD

    makedate( year( Date ), N, ( P + 1 ) ) AS Eastern;

   

LOAD

    *,

    div( H + L - ( 7 * M ) + 114, 31 ) AS N,

    mod( H + L - ( 7 * M ) + 114, 31 ) AS P;

LOAD

    *,

    div( A + ( 11 * H ) + ( 22 * L ), 451 ) AS M;

   

LOAD

    *,

    mod( 32 + ( 2 * E ) + ( 2 * I ) - H - K, 7 ) AS L;

   

LOAD

    *,

    mod( B, 4 )AS E,

    mod( ( 19 * A ) + B - G - D + 15, 30 ) AS H,

    div( C, 4 ) AS I,

    mod( C, 4 ) AS K;

   

LOAD

    *,

    mod( year( Date ), 19 ) AS A,

    mod( year( Date ), 100 ) AS C,

    div( B, 4) AS D,

    div( B - F + 1, 3 ) AS G;

   

LOAD

    *,

    div( B + 8, 25 ) AS F;

LOAD

    *,

    Date,

    div( year( Date ), 100 ) AS B

RESIDENT Date_tmp;

Schöne Grüße

Christian

MVP & Luminary
MVP & Luminary

WOW !

Natürlich etwas aufwändiger, aber vermutlich auch stabiler bei den verschiedenen Ausnahme-Regelungen. Beim nächsten Release von dieser App probiere ich es mal aus.

Schöne Grüße

Marcus

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2015-11-29 07:31 AM
Updated by: