5 Replies Latest reply: Jan 24, 2014 11:36 AM by Roman Dahl RSS

    Master Calendar without weekends and hollidays

      Hi everyone,

       

      I need to be able to show Dates exluding weekends and hollidays. Is there a simple way to do it? Thanks in advance for your help.

       

      Here is a script for my calender that I use.

       

      /*************** MinMax Table *************

      Keeps minimum and maximum Date value from Facts table

      */

       

      MinMax:

      LOAD

        Min(Date) as MinDate,

        Max(Date) as MaxDate

      RESIDENT MainTable;

       

      LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

      LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

      LET vToday = $(vMaxDate);

       

       

      /*************** Temporary Calendar *************

      Generates a single table with one field containing

      all existing dates between MinDate and MaxDate.

      */

       

      TempCal:

      LOAD

        date($(vMinDate) + rowno() - 1) AS TempDate

      AUTOGENERATE

        $(vMaxDate) - $(vMinDate) + 1;

      DROP TABLE MinMax;

       

      /*************** Master Calendar ***************

      */

       

      MasterCalendar:

      LOAD

        TempDate AS Date,

        Week(TempDate) AS Week,

        Year(TempDate) AS Year,

        Month(TempDate) AS Month,

        Day(TempDate) AS Day,

        Weekday(TempDate) AS WeekDay,

        'Q' & ceil(month(TempDate) / 3) AS Quarter,

        Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

        Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

        inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

        inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag

      RESIDENT TempCal

      ORDER BY TempDate ASC;

      DROP TABLE TempCal;

        • Re: Master Calendar without weekends and hollidays
          Marcus Sommer

          Weekends could be removed per where weekday(TempDate) < 5. Holidays should be loaded in a mapping-table before you generated your calendar and then where applymap('HolidayTable', TempDate, 'not found') <> 'not found'.

           

          where weekday(TempDate) < 5 and applymap('HolidayTable', TempDate, 'not found') <> 'not found'

           

          - Marcus

            • Re: Master Calendar without weekends and hollidays

              Hi Marcus,

              maybe I am doing something wrong but I did add that WHERE clouse... though it is still loads me the weekends. I need to get rid of those when I use Date. and then lets say i choose year and then month..  but then I only need to se the working dates... withouth weeknds


              here is my edited script with the where clouse

               

              /*************** MinMax Table *************

              Keeps minimum and maximum Date value from Facts table

               

              */

               

               

              MinMax:

              LOAD

                Min(Date) as MinDate,

                Max(Date) as MaxDate

              RESIDENT MainTable;

               

               

              LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

              LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

              LET vToday = $(vMaxDate);

               

               

              /*************** Temporary Calendar *************

               

               

              Generates a single table with one field containing

              all existing dates between MinDate and MaxDate.

               

               

              */

               

               

              TempCal:

              LOAD

                date($(vMinDate) + rowno() - 1) AS TempDate

              AUTOGENERATE

                $(vMaxDate) - $(vMinDate) + 1;

              DROP TABLE MinMax;

               

               

              /*************** Master Calendar ***************

              */

               

               

              MasterCalendar:

              LOAD

                TempDate AS Date,

                Week(TempDate) AS Week,

                Year(TempDate) AS Year,

                Month(TempDate) AS Month,

                Day(TempDate) AS Day,

                Weekday(TempDate) AS WeekDay,

                'Q' & ceil(month(TempDate) / 3) AS Quarter,

                Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

                Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

                inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

                inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag

              RESIDENT TempCal

              Where Weekday(TempDate) < 5

              ORDER BY TempDate ASC;

              DROP TABLE TempCal;

            • Re: Master Calendar without weekends and hollidays

              Hi Roman,

               

              From your script, where you have excluded weekends and holidays?

               

              Try to use If condition to check the date is weekend or not and store the value in a filed called WeekendFlag (having 1 & 0).

              Ex: If(Date = Weekend(Date),1,0) as WeekendFlag.

              Try to use Lookup for checking holidays and use another flag.

              Load the same table by eliminationg the corespoing flags WeekendFlag and HolidayFlag.