weeknumber

[lancemakali]

Hi Guys,

I'm probably asking something very stupid here, but anyway.
I would like to have an object that gives me the weeknumber of a certain date.

I managed to do that by creating a variable like this:
=Week(<Day>)

Nothing too chinese so far. But to make it even easier for the mere mortals, I wanted to add that as an object via the Designer, but I don't get it to work. No clue how to use that BO function so I can have a object in my universe directly calculating the weeknumber without people having to create a variable for that.

Any idea?

Thanks
Lance

 

[blom0344]

BO functions are report functions. If you want to add such a function to the model , then you should lookup the corresponding date/time function that is valid by the database you are using.
So, the exact answer would we dependant on the database BO communicates with. Beware, however there are 2/3 ways to calculate the week for a given date..

Ties Blom

 

[lancemakali]

Thanks for your reply;
It's an Oracle database, if that can help.

Well, basically, if at least I'd know what's hiden behind that "week()" function BO has, I could potentially translate that into an object...

but honesly, no clue.

 

[blom0344]

to_char([somedate],'ww')

It pays off to get aquinted with one's database when working with designer..

Ties Blom

 

[lancemakali]

I already tried that, but that is not giving me the result I want.
Looks at below screenshot:

- 1st column is obviously the day I would like to know the week off
- 2nd&3rd columns are actually the same things, but different formatting. These objects calculate the week but the result is not the same as if I use BO's function week()
- 4th column is the result of using what you suggested: to_char([somedate],'ww') but undoerunately is the same as col 2&3, so, not what I'm after.
- 5th column is what I would like to have. What you see here is a variable =Week(<Day>) but I would like that as an object...

Hope this is not too confusing.

Thanks in advance for your help.
Lance

 

[blom0344]

As I said, there are different ways to establish the weeknumber.

Look here for an example:

http://www.psoug.org/snippet/Get_week_number_394.htm

and convince your DBA to allow this user defined function to be created. I think you can register this function within BO and use it (or an equivalent) in Designer..

Ties Blom

 

[skrandel]

You don't even have to register it with BO. As long as it's a valid DB Function, you can use it.

Steve Krandel
Intuit