Number of the week - Oconv code

gsym · Post by **gsym** » Fri Feb 09, 2007 3:50 pm

Hi there folks,

Can anyone tell me what the conversion code is to get the number of the week in a year corresponding to a supplied internal date using OCONV ?

ray.wurlod · Post by **ray.wurlod** » Fri Feb 09, 2007 4:05 pm

There isn't one.

The reason is that the week number is determined by a business rule. On what day does a week start? How is week number 1 in a year defined (all weeks must have seven days, so "starts on Jan 1" is not a good rule).

Look at the Transform WEEK.TAG and the WeekTag routine that it calls. These document one business rule (from memory, week begins on a Monday, and week 1 is the first week that contains four calendar dates in that year).

DSguru2B · Post by **DSguru2B** » Fri Feb 09, 2007 4:06 pm

No such conversion code but not hard to do it.
If going by calender weeks, you can do something like

Code: Select all

INT(OCONV(InternalDate, "DJ")/7)

ray.wurlod · Post by **ray.wurlod** » Fri Feb 09, 2007 4:06 pm

Too simplistic, grasshopper. See above.

DSguru2B · Post by **DSguru2B** » Fri Feb 09, 2007 4:08 pm

Yea. I read your post after hitting the reply button on my end. But if the OP needs the week of the year going by calender date and not a business date, my way should work

. For other dates, more information is needed.

gsym · Post by **gsym** » Fri Feb 09, 2007 4:17 pm

oh, ok......guess I gotta find out the business rule required here then. Or just do it like DSguru suggested.

Thanks for the answers.

DSguru2B · Post by **DSguru2B** » Fri Feb 09, 2007 4:19 pm

No. Dont just do it like I suggested. If there is a business requirement to it then get that and then tackle it. If its just calender days then you can do it as I suggested.

gsym · Post by **gsym** » Fri Feb 09, 2007 4:19 pm

As far as I can ascertain, the first day of the first week starts on Jan 1st of the year.

DSguru2B · Post by **DSguru2B** » Fri Feb 09, 2007 4:20 pm

gsym wrote:As far as I can ascertain, the first day of the first week starts on Jan 1st of the year.

Confirm that from your BA. If thats the case, then you can use my code.

ray.wurlod · Post by **ray.wurlod** » Sat Feb 10, 2007 4:54 am

Alert your BA to the fact that that decision will mean that week #53 in the year has fewer than seven days, and that roll-ups by week will be invalid.

gsym · Post by **gsym** » Mon Feb 12, 2007 4:35 pm

ray.wurlod wrote:Alert your BA to the fact that that decision will mean that week #53 in the year has fewer than seven days, and that roll-ups by week will be invalid.

Yes, rollups will be a problem, never though of that. What is a possible solution for this dilemma ?

ray.wurlod · Post by **ray.wurlod** » Mon Feb 12, 2007 5:41 pm

On what day does a week start? How is week number 1 in a year defined (all weeks must have seven days, so "starts on Jan 1" is not a good rule).

Solution #1
Look at the Transform WEEK.TAG and the WeekTag routine that it calls. These document one business rule (from memory, week begins on a Monday, and week 1 is the first week that contains four calendar dates in that year).

Solution #2
Find a BA who thinks things through rather than taking the path of least resistance.

gsym · Post by **gsym** » Tue Feb 13, 2007 2:33 pm

ray.wurlod wrote:On what day does a week start? How is week number 1 in a year defined (all weeks must have seven days, so "starts on Jan 1" is not a good rule).

Solution #1
Look at the Transform WEEK.TAG and the WeekTag routine that it calls. These document one business rule (from memory, week begins on a Monday, and week 1 is the first week that contains four calendar dates in that year).

Solution #2
Find a BA who thinks things through rather than taking the path of least resistance.

Thanks Ray, solution 2 is out, doesn't look like anybody cares (but me) .......looking at solution 1