Number of Working Days between Two Dates

[Benedict]

Hello - I'm trying to create some Objects to display the number of working days between the current date and a date stored on our Oracle database. Calculating the number of days between the two dates seems pretty straightforward, but I'm having trouble constructing an Object that takes Saturdays, Sundays & Bank Holidays into account. Has anyone got any ideas how I can do this?

 

[lgolsen]

Hi.

This problem is only solved with a difficulty without a "date-table". (helpful in many situations)

1) Create a table with these columns (arbitrary-sample data included - fill out for the next 5 years or so...)

date | dayofweek | Holiday | Weekend
29.12.2000 | thu | N | N
30.12.2000 | fri | N | N
31.12.2000 | sat | N | Y
01.01.2001 | sun | Y | Y
02.01.2001 | mon | N | N

2) Join to this table on the "date" column in the designer. Should look a bit like this:

date_table.date between tab1.date and current date

3) Create a measure object in the universe that counts the number of rows in the date_table with holiday ='N' and Weekend='N'

...

I hope this is clear enough...