How to find Week Number for the given date range

building reports webintelligence
1

Hi Gurus,

I have a requirement to disply the week numbers between given date ranges.

For ex: if the user gives the date Range 01-Jun-2011 to 30-June-2011
i need the result like this.

Week1(01-June to 5 June)
Week2(06-June to 12 June)
…
…
…
Week5(27- june to 30 June)

In webintelligence we have function for week, but it is giving the week number ofr the year not for the month.

Any idea how can i reset the Week number for the month.

Thanks for the help!!!

lovebo :india: (BOB member since 2010-10-06)

2

Hi,

The easiest solution is to have this information stored in a calendar table in a database and use it.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

You can create an object in the universe

Say, Week Object = to_char(date_string,‘W’)

W - Gives Week number of the date string in the month
WW - Gives Week number of the date string in the year

Note: this is Oracle syntax

jprasanthram :switzerland: (BOB member since 2010-12-10)

4

Thanks guys for your valuable suggestions!!!

But unfortunately i don’t have access for Universe.

So is there any other option to do this in WEBI?

Thanks

lovebo :india: (BOB member since 2010-10-06)

5

Use WebI function FormatDate([Date Object];“W”)

jprasanthram :switzerland: (BOB member since 2010-12-10)

6

Not really a weeknumber but a weeksequence in the month that your looking for.
Where days 1 to 7 is week 1, days 8 - 14 week 2 and so on?
Using week() means that it looks at calendar weeks and not 1-7 is week 1.

If its always a month that is selected you can simply use
=ceil(DayNumberOfMonth(date)/7)

If you can select more months I would suggest adding monthnumber infront:
=formatnumber(MonthNumberOfYear(date);“00”) + formatnumber(ceil(DayNumberOfMonth(date)/7);“00”)
Become 0105, or 1101 etc.
(Check if its 00 or ## to get 2chars, i always forget)

And if you go over years add that the last 2 from year infront of it aswell.
100105, 101101, 110402
year - month in year - week in month

IngeH :netherlands: (BOB member since 2004-06-22)

7

HI Inge Heijnen

thanks so much for your reply, i am not getting the point clear. can you pls explain me.
My objective is i have to display the week name for the given date range.

That is like Week1;Week2Week3…

lovebo :india: (BOB member since 2010-10-06)

8

Just to follow up with the post, following solution worked for me:

=Week([])-Min(Week([])) In Block+1

and my block looks for a date range selected by the user in the prompt input.

I hope it helps someone who refers this post.

bdharma (BOB member since 2008-07-07)

9

I found a bug in the above solution to find the week number in a date range, if the date range falls between 2 years,

Following solution may work better:

Var_Week Nbr_New

=If(([] <= [VAR_Last Day_Min Yr]) Or ((RelativeDate([];-7) <= [VAR_Last Day_Min Yr]) And Week(])=Week([VAR_Last Day_Min Yr]));Week([])-Week(Min([])) In Block+1;Week([VAR_Last Day_Min Yr])-Week(Min([]) In Block)+1+Week([]))

VAR_Last Day_Min Yr

=(ToDate(Concatenation(FormatNumber(Year(Min([]) In Block);"####");“1231”);“yyyyMMdd”))

bdharma (BOB member since 2008-07-07)