My Oracle Support Banner

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

Last updated on MARCH 20, 2019

Applies to:

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


On : 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 ?


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.