Last WorkDay of Month

[avastani]

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?

[Anonymous · Accepted solution]

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

[swuehl]

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

[avastani]

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.