WEEK_OF_YEAR() OBIEE function not returning ISO week numbers for DATETIME typed columns in the RPD (Doc ID 2161068.1)

Last updated on JULY 27, 2017

Applies to:

Oracle Fusion Sales - Version 11.1.10.0.0 and later
Information in this document applies to any platform.

Goal

On : 11.1.10.0.0 version, Analytics


Is there a way to modify "week_of_year()" function to apply ISO week numbering?


You are using a date filter in BI, specifically for finding the 'week number in year' value, via the WEEK_OF_YEAR() OBIEE function.
If needing to filter on the 17th week of the week, you have to set 18 for week number in the filter.

For instance, using "Sales - Opportunities and CRM Products real time" subject area and setting WEEK_OF_YEAR("Opportunity"."Created Date"), it returns the "correct week + 1".

---------------------

The problem is that week_of_year() returns the US-way of numbering the weeks in a year.
But, other countries like France and Europe are using ISO-way of numbering weeks in a year.

 - In US numbering, the 1st week of 2016 is from December 28, 2015 to January 3, 2016, because the 1st week of year is the week that contains the 1st of January and a week is beginning with Sunday.
 - In ISO numbering, the 1st week of 2016 is the next one : from January 4, 2016 to January 10, 2016, because in ISO-definition, The first week of the year is the week that contains that year's first Thursday (='First 4-day week').

The shift appears on years 2016, then 2021, 2022, 2025, 2027.


So, is there a way to modify "week_of_year()" function to apply ISO ?

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms