The CALENDARWEEK option determines whether weeks should be aligned with the actual calendar year.
Note:
You can only use this function with dimensions of type WEEK.(Default) Specifies that weeks are aligned with the calendar year. For example, if you have defined a dimension of type WEEK, Oracle OLAP numbers its values so that the first week in the calendar year is week 1, the second week in the calendar year is week 2, and so on. Weeks are aligned with the calendar year regardless of any beginning or ending date specified in the WEEK dimension definition.
Specifies that weeks are not aligned with the calendar year. Instead, weeks are numbered so that they are aligned with the date specified in the dimension definition. For example, if you have defined a dimension of type WEEK with a beginning or ending date, its values are numbered so that the week corresponding to the date in the dimension definition is week 1, the following week is week 2, and so on.
Setting CALENDARWEEK to NO causes weeks to be numbered so that the number 1 is assigned to the week beginning or ending on the date specified in the DEFINE DIMENSION statement. This week is then assigned to a fiscal year, which is the calendar year of the first January 1 on or after the week's starting date. For example, if you define a dimension of type WEEK with a starting date of 02Jan1996 (or, equivalently, an ending date of 08Jan1996), the week starting 02Jan1996 is considered week 1 of fiscal year 1997. If, by contrast, you had given the dimension a starting date between 02Jan1995 and 01Jan1996, then the week starting on that date is week 1 of fiscal year 1996.
Example 5-7 Aligning Weeks with the Calendar Year
The following statements define a dimension of type WEEK, define its ending date, add values to the dimension, and produce a report.
DEFINE week dimension WEEK ENDING '18Jan97' MAINTAIN week ADD '21Dec96' '25Jan97' REPORT W 22 CONVERT(week DATE)
These statements produce the following output.
WEEK CONVERT(WEEK DATE) -------------- -------------------- w51.96 21Dec96 w52.96 28Dec96 w1.97 04Jan97 w2.97 11Jan97 w3.97 18Jan97 w4.97 25Jan97
Example 5-8 Aligning Weeks with a Specified Ending Date
The following statements set the CALENDARWEEK option to NO, which aligns the weeks with the ending date that is specified in the definition of the week dimension in "Aligning Weeks with the Calendar Year".
CALENDARWEEK = NO REPORT W 22 CONVERT(week date)
These statements produce the following output.
WEEK CONVERT(WEEK DATE) -------------- -------------------- w50.97 21Dec96 w51.97 28Dec96 w52.97 04Jan97 w53.97 11Jan97 w1.98 18Jan97 w2.98 25Jan97