Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.
Having different holidays by country would be more complicated than what I did in that thread, though. Attached is one way to do it. Again, we're generating a holiday list in a variable, but now we generate a pick(match()) to select the right holiday list by country. I don't like that I have a separate load for each country, but I can't think of how to get around it. It probably won't be a problem in practice since the tables involved at that point are small.
I completely made up these holidays, so don't look for them to make any sense.
Here's the script:
[Holidays]: LOAD * INLINE [ Country, Holiday USA, 3/15/2011 USA, 3/10/2011 USA, 2/15/2011 USA, 1/1/2011 Mexico, 1/10/2011 Mexico, 2/15/2011 Mexico, 3/4/2011 ];
Countries: LOAD text(fieldvalue('Country',recno())) as Country ,recno() as Sequence AUTOGENERATE fieldvaluecount('Country') ; AllCountries: LOAD concat(chr(39) & Country & chr(39),',',Sequence) as AllCountries RESIDENT Countries ; LET allholidays = '=pick(match(Country,' & peek('AllCountries') & ')' ; FOR I = 1 TO noofrows('Countries') AllHolidays: LOAD concat(num(Holiday),',') as AllHolidays RESIDENT Holidays WHERE Country = peek('Country',$(I)-1,'Countries'); LET allholidays = allholidays & ',' & chr(39) & peek('AllHolidays') & chr(39); DROP TABLE AllHolidays; NEXT
LET allholidays = allholidays & ')';
DROP TABLES Countries ,AllCountries ;
[Dates]: LOAD * INLINE [ Date 03/05/2011 03/14/2011 03/17/2011 ];
Then I made a list box with always one Country selected, and I used expressions like these.
firstworkdate(Date,4,$(allholidays)) // in a straight table by Date
Heh. I meant to drop the Holiday table, then left it so you could see what the holidays actually were. But if you're leaving it in the data model, then there's a MUCH simpler way to handle things. Just define your variable like this:
