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:

     

    let vOstern = round(num(date(makedate(year($(vStart) + $(i)), 4, day(minute(year($(vStart) + $(i)) / 38) / 2 + 55)), 'DD.MM.YYYY')) / 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

     

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


    Marcus Sommer