Working with holidays and not working days

[gpbarsky]

Hi my friends.....

I need to make some operations (add, subtract) using days, but I must consider working days and not working days.

Does anybody know if there are functions in DS in order to get the "holiday status" of a given date ?

And besides, I need this function to take into account that for adding a number of days, it should consider that in the middle it may exist several not working days.

Example: let us calculate a given Thursday plus 5 days.

If the addition is using calendar days, the result is the next Tuesday.

If the addition is using working and not working days, the result is the next Thursday.

Am I clear what I need ?

Thanks in advance.

[ogmios]

The default "Kimbalistic reply" for the holiday status of a day is to use a date table and join on that to get your holidays. Holidays differ per country, so there's never going to be a general magic function to calculate it all.

Ogmios

[kcbland]

Business days differential is really easy to do. When you setup your time dimension, you have to have a column that is the total number of business days since day #1.

For example, your first date in the time dimension has the value 1. The next business day has the value 2. Keep assigning the value as you chronologically scan the table. By physically recording the elapsed days since day one, you can take the values on two rows and subtract them. This gives you the number of business days between.

Simply expand your time dimension to hold this extra column.

[gpbarsky]

Ken:

I'm sorry, but I really didn't understand what you explained me. Could you give me an example ?

[kcbland]

Build a Time dimension table as Kimball recommends. Have a column that is BusinessDayIndicator and set it to 1 on all appropriate days. For days that are holidays you'll want a holiday indicator as well.

Add another column to contain the integer number of business days since days 1. Call it something like 'BusinessDayIndex'.

Select all rows from your time dimension, where BusinessDayIndicator = 1, order by the date ascending. Assign the rownumber to the BusinessDayIndex.

Now, anytime you need to know the number of business days elapsed between two dates, simply do two joins to your time dimension using your different dates (use 2 aliases to your time dimension). Subtract the BusinessDayIndex value from both returned rows and you will have the number of business days elapsed.