SYMPTOM:
When the same expression “Week(CurrentDate()-x)” is used into definition of attribute and derived metric, for some particular date, the values are different. For example, August 4th, 2014, the attribute will show value “31”, while, in derived metric, the value is “32”, as shown below:
This issue occurs when using Oracle data warehouse, it cannot be reproduced in Tutorial Project which based on Access database.
CAUSE:
There are two definitions of Week, one way is based on ISO standard, so the first week of year 2014 is from December 30th, 2013 to January 5th, 2014. The other way is defined as week of this year, so the first week of year 2014 is from January 1st, 2014 to January 7th, 2014. In Oracle database, these two ways correspond to parameters “IW” and “WW”.
In Strategy Analytics Developer, the attribute definition is handled by SQL Engine. Week() function will use parameter “WW” into calculation against Oracle warehouse. The SQL statement is shown below:
SQL Statement: select distinct TO_NUMBER(TO_CHAR((TRUNC(CURRENT_DATE) - 26), 'WW')) CustCol_12 from LU_DAY a11
