2 Replies Latest reply: Apr 29, 2014 12:59 AM by Uchiha Itachi RSS

    Help creating Calendar that excludes weekends and holidays

      Hello,

       

      I've run into a bit of trouble when trying to create a calendar that excludes holidays and weekends. I have this working code for a regular calendar:

       

      LET vMaxDate = num(today());

      LET vMinDate = num(Date#('25/02/2008', 'DD/MM/YYYY'));

       

       

      //Build the calendar with most date dimensions

      LET vToday=today(1);

      Calendar:

      LOAD

             Date($(vMinDate)+Iterno()-1) as InvoiceDate

             ,Date($(vMinDate)+Iterno()-1) as DateDual

             ,Day($(vMinDate)+Iterno()-1) as Day

             ,week($(vMinDate)+Iterno()-1) as Week

             ,Month($(vMinDate)+Iterno()-1) as Month

             ,Year($(vMinDate)+Iterno()-1) as Year

             ,MonthName($(vMinDate)+Iterno()-1) as MonthYear

             ,WeekName($(vMinDate)+Iterno()-1) as WeekYear

             ,WeekDay($(vMinDate)+Iterno()-1) as WeekDay

             ,WeekStart($(vMinDate)+Iterno()-1) as WeekStart

             ,QuarterName($(vMinDate)+Iterno()-1) as Quarter

      AUTOGENERATE 1 WHILE RowNo() <= ($(vMaxDate)-$(vMinDate));

       

       

      Ranges:

      LOAD

             Range

             ,date(evaluate(RangeStart)) as RangeStart

             ,date(evaluate(RangeEnd)) as RangeEnd;

      LOAD * INLINE [

      Range; RangeStart; RangeEnd;

      This Week; WeekStart(vToday); WeekEnd(vToday);

      Last Week; WeekStart(vToday,-1); WeekEnd(vToday, -1);

      Last Two Weeks; WeekStart(vToday,-2); WeekEnd(vToday,-1);

      ]

      (delimiter is ';');

      JOIN (Ranges) IntervalMatch (InvoiceDate) LOAD RangeStart, RangeEnd RESIDENT Ranges;

       

       

       

      However, I need something that will only count weekdays, excluding holidays. In addition, for a few of the holidays (like Thanksgiving, etc) don't always fall on the same date, but rather on the same day (third Thursday of the year, etc).

       

      Any help would be greatly appreciated. Thank you!!