3 Replies Latest reply: Aug 21, 2012 11:26 PM by Amirali Vastani RSS

    Last WorkDay of Month

    Amirali Vastani

      Is there a predefined function to determine the last workday of any month in a given year?

       

      I am trying to create the Stock Market Calendar where for instance, if AsOfDate = June, 28, 2012

       

      then, 1 Month ago = May, 25, 2012 (i.e. the last business day a month ago since AddMonths(AsOfDate, -1) = May, 28, 2012 which is a holiday and therefore, the max business day before that is Friday May 25, 2012.

       

      I am thinking I will need a reference table of all the holidays in the years before and years to come. But a predefined function for shortcuts to Prior Workday, Next Workday, WorkDay 1 month ago, Last Workday of month etc would be helpful.

       

      Thoughts anyone?

        • Re: Last WorkDay of Month
          Michael Solomovich

          Sometimes I create a work day flag like this:

           

          LEFT JOIN (Date) LOAD DISTINCT
          DateID,
          1 as WorkDayFlag
          RESIDENT Date
          WHERE weekday(DateID)<5                                   // not Saturday or Sunday
          AND match(date(DateID), $(HolidayList))=0;          // not holiday

           

          As you can guess, variable HolidayList is a comma-separated list of holidays.  So, the last work day of month is easy, it is max Date per month where WorkDayFlag=1.
          As for the next and previous work day, try this:
          previous:   firstworkdate(BaseDate, 1, $(HolidayList))
          next:   lastworkdate(BaseDate, 1, $(HolidayList))


          Regards,
          Michael

            • Re: Last WorkDay of Month
              Amirali Vastani

              this is good stuff. Both suggestions worked and actually refined my solution.

               

              The problem I am trying to solve is that I have calendar dates in my fact table from 1976 all the way to today and I am trying to create an asOfDate table which is a join of the MasterCalendar onto itself (Clearly Extremely Inefficient)

               

              1. have to use set analysis

              2. there is asofdate full outer join onto the MasterCalendar table so I have joins for records that have anything beyond Prior5YearFlag as 0. so in essence if I have asofdate of today, then Jan 1 2000 is 12 years ago and I am not interested in Flags which are more than 5 rolling years away from the as of date.

              3. with every new business day, this table has to be recreated/appended to.

              4. this table itself adds tot he ultimate size of the app

               

              so i think this is the most inefficient way to work with as of date. exploring other options.

            • Re: Last WorkDay of Month
              Stefan Wühl

              There are indeed some QV functions you can make use of, namely firstworkdate():

               

              firstworkdate(end_date, no_of_workdays {, holiday} )

              Returns the latest starting date to achieve number_of_workdays (Monday-Friday) ending no later than end_date taking into account any optionally listed holidays. End_date and holiday should be valid dates or timestamps.

              Examples:

              firstworkdate ('2007-03-01', 9) returns '2007-02-19' 

              firstworkdate ('2006-12-31', 8, '2006-12-25', '2006-12-26') returns '2006-12-18' 

               

              so

              =firstworkdate(monthend(today()),1)

               

              returns 2012-08-31, which seems to be correct (last friday in August), and

               

              =firstworkdate(addmonths(monthend(today()),1),1)

               

              returns the last work date in September (2012-09-28). There is also a function called lastworkdate, but I believe you need firstworkdate function here (check out the other and all the other date and time functions in the Help).

               

              You can add a list of holidays to the function as shown above to take care of your holidays, too.

               

              Hope this helps,

              Stefan