Sometimes I create a work day flag like this:
LEFT JOIN (Date) LOAD DISTINCT
1 as WorkDayFlag
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))
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.
There are indeed some QV functions you can make use of, namely firstworkdate():
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.
firstworkdate ('2007-03-01', 9) returns '2007-02-19'
firstworkdate ('2006-12-31', 8, '2006-12-25', '2006-12-26') returns '2006-12-18'
returns 2012-08-31, which seems to be correct (last friday in August), and
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,