WEEK_OF_YEAR() OBIEE function not returning ISO week numbers for DATETIME typed columns in the RPD
Last updated on JULY 27, 2017
Applies to:Oracle Fusion Sales - Version 220.127.116.11.0 and later
Information in this document applies to any platform.
On : 18.104.22.168.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 ?
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms