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-leap-year. 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