Weekday Function

[Smithsc]

I am looking to create a field in a BO universe that will calculate the number of working days between to dates and I can't seem to find a function to do this. I have looked in other posts and people have mentioned calendar tables but I don’t know what these are and how to use them.
Could someone please explain how (or point me in the right direction) to create a calendar table and then use it in a universe. I am using BO Universe designer XI.

Many thanks for any help

 

[skrandel]

This is not really a BO issue. The functions available to you are those from the database you are connecting to. So, if you can do it in SQL (or with a function), you can do it in the universe.

Steve Krandel
Intuit

 

[blom0344]

The trick is to indeed use a calender table that stores a special index for workdays.

You can roll your own calenderfile from Excel 2007 by using the NETWORKDAYS function:

=NETWORKDAYS(DATE(2000,1,1);DATE(2000,1,A2))

If you make sure that the A column stores sequences from 1 to any number high enough for you purpose.

A result would be :

1 1-1-2000 0 1 SU
2 2-1-2000 1 2 MO
3 3-1-2000 2 3 TU
4 4-1-2000 3 4 WE
5 5-1-2000 4 5 TH
6 6-1-2000 5 6 FR
7 7-1-2000 5 7 SA
8 8-1-2000 5 1 SU
9 9-1-2000 6 2 MO
10 10-1-2000 7 3 TH
11 11-1-2000 8 4 WE
12 12-1-2000 9 5 TH
13 13-1-2000 10 6 FR

The third column holds the special index and you will notice that it increments with 1 when the next day is a working day. Otherwise it does not increment.

You can upload such a Excel file to a database table and use it (with alias) in the universe.

Ties Blom