Week function behaviour in BO XI 3.1

[Former Member]

Concern when Week function is used in below scenario:

Using Week function in one of the reports for the date 1/1/2012 (Conversion into date time everything is done) ,(mm/dd/yy format)

we expected  the function to return value 1  ,but its returning 52 ,why is it so? (For 1/1/2012 ...1st falls on sunday,so is it the tool considering the date as in previous year';s last week?)

Reason:

The Week function considers monday as the first day and that is the reason you get 52 as the answer. Week function in BO follows the ISO standards:

http://en.wikipedia.org/wiki/ISO_week_date

so  acc: to ISO standard:

• If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year.

If non-default behaviour is required:

If you need non default behaviour then it's best to let the database do the work.  Please see:

1207092 - How to change the week number to International Standards Organization (ISO) standard or Am...

For information.  Create a universe objects with SQL that does the calculation for you - this will vary depending on the database.

Note that the KBA is about 6.5, XI R2 and desktop intelligences but its also applicable to Webi in XI 3.1.

If Requirement is like:

Need weekly data of employees who joined in 1st week of  2012 ,but the emp who joined on 1/1/2012 donot cme in the current list .

Apart from using other logic and functions ,can we acheive the result using Week functioion??

Various Approaches:

If they joined on the 31/12/2011, would you expect them to be in week 1 of 2012 or week 52 of 2011.

1) None of the 'week' variants will split those up.  Using any of the ISO/American/BOE week definitions function will put both those dates in the same week 'bucket' (even though the definition is slightly different for each (again see the KBA I mentioned).

2) It seems you are trying to use one type of group (week) when you actually need two.  In reality it seems you need to filter the user by year rather than week and then group the table by week.

3) just have a prompt for the year and say where the year is 2012?

4) The truth of the matter is that in your business logic,   Jan 1st is the first day of the first week of the year, regardless of the situation. Which is absolutely true. Often it will end on the 52nd week of the previous year so we have 6/7 of chances that Week 52 of Year X is as same as Week 1 of Year X+1

The solution for you is very easy, use something like this:

=If (Month([MyDate])=1 And Week([MyDate])=52) Then 1 Else Week([MyDate])

If the month is January and the week is 52 it will return 1 rather than 52.

So ,the above appraoches can be taken according to the requirement,

Thanks.