Step by Step Guide to Fiscal Week and Fiscal Quarter
Difference
1. Create a custom InfoObject “ZFISCWEEK” of Data Type “NUMC” length 6.
2. Add “ZFISCWEK” in the given DSO / Cube.
3. Create the transformation rule with type “Routine” with Date (in our example FKDAT – Bill date)
and FY Variant (PERIV) as Source Field Assignment.
4. The logic would be as follows:
a. First get the year from the Date or use standard Function Module
“DATE_TO_PERIOD_CONVERT”.
b. Find the first and last date of a given Year. Use standard SAP Function Module,
“FIRST_AND_LAST_DAY_IN_YEAR_GET” for this.
c. Find the difference of days in a given Fiscal Year i.e. No. of days between First Date and the
given data record date. Use standard SAP Function Module,
“/SDF/CMO_DATETIME_DIFFERENCE” for this.
d. Divide the No. of Days by 7 to get the total number of weeks. If Weeks is greater than say 52.5, It is automatically rounded off to 53, however if it is less than 52.5, it rounds it off to 52 and we have to add 1 to it explicitly to show 53. Therefore, we are using dummy variable (NUMBER) to find out when we have to add 1 explicitly.
5. To get the Fiscal Quarter find the week as above procedure and then find the Quarter based on the range of 13 weeks.
Fiscal Week Code:
DATA: YEAR (4) TYPE N, DATE1 LIKE SY-DATUM, DATE2 LIKE SY-DATUM, DAYS TYPE P,
WEEKS (2) TYPE N,
NUMBER (2) TYPE N, FISCV (2) TYPE C.
CLEAR: YEAR, DATE1, DATE2, DAYS, WEEKS, NUMBER, FISCV.
DATE2 = SOURCE_FIELDS-FKDAT. FISCV = SOURCE_FIELDS-PERIV.
**Get the Year from Date
CALL FUNCTION 'DATE_TO_PERIOD_CONVERT' EXPORTING
I_DATE = DATE2
* I_MONMIT = 00
I_PERIV = FISCV
IMPORTING
* E_BUPER =
E_GJAHR = YEAR
EXCEPTIONS
INPUT_FALSE = 1
T009_NOTFOUND = 2
T009B_NOTFOUND = 3
OTHERS = 4.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
**Get the First and Last date of the Year
CALL FUNCTION 'FIRST_AND_LAST_DAY_IN_YEAR_GET' EXPORTING
I_GJAHR = YEAR
I_PERIV = FISCV
IMPORTING
E_FIRST_DAY = DATE1
* E_LAST_DAY = DATE2
EXCEPTIONS
INPUT_FALSE = 1
T009_NOTFOUND = 2
T009B_NOTFOUND = 3
OTHERS = 4.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
**Get the difference between the first and input date (given data record date)
CALL FUNCTION '/SDF/CMO_DATETIME_DIFFERENCE' EXPORTING
DATE1 = DATE1
* TIME1 =
DATE2 = DATE2
* TIME2 =
IMPORTING
DATEDIFF = DAYS
* TIMEDIFF =
* EARLIEST =
EXCEPTIONS
INVALID_DATETIME = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
**Get the rounded off value to get the total number of weeks.
DAYS = DAYS + 1.
WEEKS = DAYS / 7.
NUMBER = DAYS MOD 7.
IF NUMBER > 0 AND NUMBER < 4.
WEEKS = WEEKS + 1.
ENDIF.
**Get the Fiscal week for an input date by concatenating Year and Week found in above step
CONCATENATE YEAR WEEKS INTO RESULT.
Fiscal Quarter Code:
DATA: YEAR (4) TYPE N, DATE1 LIKE SY-DATUM, DATE2 LIKE SY-DATUM, DAYS TYPE P,
WEEKS (2) TYPE N, NUMBER (2) TYPE N, FISCV (2) TYPE C.
CLEAR: YEAR, DATE1, DATE2, DAYS, WEEKS, NUMBER, FISCV. DATE2 = SOURCE_FIELDS-FKDAT.
FISCV = SOURCE_FIELDS-PERIV.
**Get the Year from Date
CALL FUNCTION 'DATE_TO_PERIOD_CONVERT' EXPORTING
I_DATE = DATE2
* I_MONMIT = 00
I_PERIV = FISCV
IMPORTING
* E_BUPER =
E_GJAHR = YEAR
EXCEPTIONS
INPUT_FALSE = 1
T009_NOTFOUND = 2
T009B_NOTFOUND = 3
OTHERS = 4.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
**Get the First and Last date of the Year
CALL FUNCTION 'FIRST_AND_LAST_DAY_IN_YEAR_GET' EXPORTING
I_GJAHR = YEAR
I_PERIV = FISCV
IMPORTING
E_FIRST_DAY = DATE1
* E_LAST_DAY = DATE2
EXCEPTIONS
INPUT_FALSE = 1
T009_NOTFOUND = 2
T009B_NOTFOUND = 3
OTHERS = 4.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
**Get the difference between the first date and input date (given data record date)
CALL FUNCTION '/SDF/CMO_DATETIME_DIFFERENCE' EXPORTING
DATE1 = DATE1
* TIME1 =
DATE2 = DATE2
* TIME2 =
IMPORTING
DATEDIFF = DAYS
* TIMEDIFF =
* EARLIEST =
EXCEPTIONS
INVALID_DATETIME = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
**Get the rounded off value to get the total number of weeks.
DAYS = DAYS + 1.
WEEKS = DAYS / 7.
NUMBER = DAYS MOD 7.
IF NUMBER > 0 AND NUMBER < 4.
WEEKS = WEEKS + 1.
ENDIF.
**Get the Quarter based on the found week in above step for an input date (given record date)
IF WEEKS BETWEEN '01' AND '13'.
CONCATENATE YEAR '1' INTO RESULT.
ELSEIF WEEKS BETWEEN '14' AND ’26’.
CONCATENATE YEAR '2' INTO RESULT.
ELSEIF WEEKS BETWEEN '27' AND ’39’.
CONCATENATE YEAR '3' INTO RESULT.
ELSEIF WEEKS BETWEEN '40' AND ’53’.
CONCATENATE YEAR '4' INTO RESULT.
Appendix
Other useful Function Modules for dates: RSARCH_DATE_SHIFT: To get the Date/Week Shift
Enter the date. (I_DATE)
Enter the Shift unit i.e. “Shift by DAY”, “Shift by Week” etc. (I_SHIFT) Enter the Shift Unit i.e. 1,2,3…etc (I_SHIFT_UNIT)
Enter the Option i.e. LT (Less Than), GT (Greater Than) etc. (I_OPTION)
FIMA_DATE_SHIFT_WITH_WEEKDAY: To get the Next day as well next week days
Enter the date (I_DATE).
Enter the Shift unit in I_WEEKDAY. If value is „1‟ then it will show Monday of the Next week and if any other number then it will show the according day.
Enter, if required, Number of weeks shift in I_NUMBER_OF_WEEKDAYS.
If you want to stay in the current month only then only make I_FLG_STAY_IN_MONTH = X else leave it blank.
To understand this FM let us take following example:
Example 1:
I_DATE = 09/06/2010
I_WEEKDAY = 1
I_NUMBER_OF_WEEKDAYS = 0
I_FLG_STAY_IN_MONTH = „„
If I_WEEKDAYS = 1 & I_NUMBER_OF_WEEKDAYS = n, where n is any whole number, then it will always show nth week‟s Monday irrespective of date.
Example 2:
I_DATE = 09/06/2010
I_WEEKDAY = 2
I_NUMBER_OF_WEEKDAYS = 0
I_FLG_STAY_IN_MONTH = „„
If I_WEEKDAY > 1 & I_NUMBER_OF_WEEKDAYS = n, where n is any whole number, then it will show nth week date based on I-WEEKDAY value.
If entered date is Monday, I_NUMBER_OF_WEEKDAYS = 0 and I_WEEKDAY = 2 then it will show the same week Tuesday i.e. 09/07/2010 in our example.
If entered date is Monday, I_NUMBER_OF_WEEKDAYS = 3 and I_WEEKDAY = 2 then it will show after 2 week‟s Tuesday i.e. 09/20/2010 in our example.
Like this few more combinations can be made as per the requirements. WEEK_GET_FIRST_DAY: To get the First date of the Calendar Week
This article addresses the requirement of Fiscal Week and Fiscal quarter in BW/BI Reports.
Business Requirement
In quite a few of the Sales requirement, users will ask for Calendar week analysis and reports based on
Calendar week. Such requirements will be satisfied using standard time characteristics vise 0CALWEEK,
0CALQUARTER.
But in certain scenarios, users will ask for Fiscal Week and to achieve the same we need to write logic based on the Date, Fiscal Year and Fiscal Year variant.
Difference
If, for a given client, the Fiscal Year (FY) is not same as that maintained in standard SAP system then Fiscal week and Calendar week would be different.
For example, if SAP standard FY Variant K4 is used then the FY would be January to December but if for a
client the Fiscal Year is, say July to June, and say defined FY Variant is “XY” then in BW we can neither directly use 0CALWEEK for Weekly reporting requirements nor can directly have any time conversion, instead we need to derive Fiscal Week code based on the Date, FY and FY Variant.
Step by Step guide1. Create a custom InfoObject “ZFISCWEEK” of Data Type “NUMC” length 6.
2. Add “ZFISCWEK” in the given DSO / Cube.
3. Create the transformation rule with type “Routine” with Date (in our example FKDAT – Bill date)
and FY Variant (PERIV) as Source Field Assignment.
4. The logic would be as follows:
a. First get the year from the Date or use standard Function Module
“DATE_TO_PERIOD_CONVERT”.
b. Find the first and last date of a given Year. Use standard SAP Function Module,
“FIRST_AND_LAST_DAY_IN_YEAR_GET” for this.
c. Find the difference of days in a given Fiscal Year i.e. No. of days between First Date and the
given data record date. Use standard SAP Function Module,
“/SDF/CMO_DATETIME_DIFFERENCE” for this.
d. Divide the No. of Days by 7 to get the total number of weeks. If Weeks is greater than say 52.5, It is automatically rounded off to 53, however if it is less than 52.5, it rounds it off to 52 and we have to add 1 to it explicitly to show 53. Therefore, we are using dummy variable (NUMBER) to find out when we have to add 1 explicitly.
5. To get the Fiscal Quarter find the week as above procedure and then find the Quarter based on the range of 13 weeks.
Fiscal Week Code:
DATA: YEAR (4) TYPE N, DATE1 LIKE SY-DATUM, DATE2 LIKE SY-DATUM, DAYS TYPE P,
WEEKS (2) TYPE N,
NUMBER (2) TYPE N, FISCV (2) TYPE C.
CLEAR: YEAR, DATE1, DATE2, DAYS, WEEKS, NUMBER, FISCV.
DATE2 = SOURCE_FIELDS-FKDAT. FISCV = SOURCE_FIELDS-PERIV.
**Get the Year from Date
CALL FUNCTION 'DATE_TO_PERIOD_CONVERT' EXPORTING
I_DATE = DATE2
* I_MONMIT = 00
I_PERIV = FISCV
IMPORTING
* E_BUPER =
E_GJAHR = YEAR
EXCEPTIONS
INPUT_FALSE = 1
T009_NOTFOUND = 2
T009B_NOTFOUND = 3
OTHERS = 4.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
**Get the First and Last date of the Year
CALL FUNCTION 'FIRST_AND_LAST_DAY_IN_YEAR_GET' EXPORTING
I_GJAHR = YEAR
I_PERIV = FISCV
IMPORTING
E_FIRST_DAY = DATE1
* E_LAST_DAY = DATE2
EXCEPTIONS
INPUT_FALSE = 1
T009_NOTFOUND = 2
T009B_NOTFOUND = 3
OTHERS = 4.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
**Get the difference between the first and input date (given data record date)
CALL FUNCTION '/SDF/CMO_DATETIME_DIFFERENCE' EXPORTING
DATE1 = DATE1
* TIME1 =
DATE2 = DATE2
* TIME2 =
IMPORTING
DATEDIFF = DAYS
* TIMEDIFF =
* EARLIEST =
EXCEPTIONS
INVALID_DATETIME = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
**Get the rounded off value to get the total number of weeks.
DAYS = DAYS + 1.
WEEKS = DAYS / 7.
NUMBER = DAYS MOD 7.
IF NUMBER > 0 AND NUMBER < 4.
WEEKS = WEEKS + 1.
ENDIF.
**Get the Fiscal week for an input date by concatenating Year and Week found in above step
CONCATENATE YEAR WEEKS INTO RESULT.
Fiscal Quarter Code:
DATA: YEAR (4) TYPE N, DATE1 LIKE SY-DATUM, DATE2 LIKE SY-DATUM, DAYS TYPE P,
WEEKS (2) TYPE N, NUMBER (2) TYPE N, FISCV (2) TYPE C.
CLEAR: YEAR, DATE1, DATE2, DAYS, WEEKS, NUMBER, FISCV. DATE2 = SOURCE_FIELDS-FKDAT.
FISCV = SOURCE_FIELDS-PERIV.
**Get the Year from Date
CALL FUNCTION 'DATE_TO_PERIOD_CONVERT' EXPORTING
I_DATE = DATE2
* I_MONMIT = 00
I_PERIV = FISCV
IMPORTING
* E_BUPER =
E_GJAHR = YEAR
EXCEPTIONS
INPUT_FALSE = 1
T009_NOTFOUND = 2
T009B_NOTFOUND = 3
OTHERS = 4.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
**Get the First and Last date of the Year
CALL FUNCTION 'FIRST_AND_LAST_DAY_IN_YEAR_GET' EXPORTING
I_GJAHR = YEAR
I_PERIV = FISCV
IMPORTING
E_FIRST_DAY = DATE1
* E_LAST_DAY = DATE2
EXCEPTIONS
INPUT_FALSE = 1
T009_NOTFOUND = 2
T009B_NOTFOUND = 3
OTHERS = 4.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
**Get the difference between the first date and input date (given data record date)
CALL FUNCTION '/SDF/CMO_DATETIME_DIFFERENCE' EXPORTING
DATE1 = DATE1
* TIME1 =
DATE2 = DATE2
* TIME2 =
IMPORTING
DATEDIFF = DAYS
* TIMEDIFF =
* EARLIEST =
EXCEPTIONS
INVALID_DATETIME = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
**Get the rounded off value to get the total number of weeks.
DAYS = DAYS + 1.
WEEKS = DAYS / 7.
NUMBER = DAYS MOD 7.
IF NUMBER > 0 AND NUMBER < 4.
WEEKS = WEEKS + 1.
ENDIF.
**Get the Quarter based on the found week in above step for an input date (given record date)
IF WEEKS BETWEEN '01' AND '13'.
CONCATENATE YEAR '1' INTO RESULT.
ELSEIF WEEKS BETWEEN '14' AND ’26’.
CONCATENATE YEAR '2' INTO RESULT.
ELSEIF WEEKS BETWEEN '27' AND ’39’.
CONCATENATE YEAR '3' INTO RESULT.
ELSEIF WEEKS BETWEEN '40' AND ’53’.
CONCATENATE YEAR '4' INTO RESULT.
Appendix
Other useful Function Modules for dates: RSARCH_DATE_SHIFT: To get the Date/Week Shift
Enter the date. (I_DATE)
Enter the Shift unit i.e. “Shift by DAY”, “Shift by Week” etc. (I_SHIFT) Enter the Shift Unit i.e. 1,2,3…etc (I_SHIFT_UNIT)
Enter the Option i.e. LT (Less Than), GT (Greater Than) etc. (I_OPTION)
FIMA_DATE_SHIFT_WITH_WEEKDAY: To get the Next day as well next week days
Enter the date (I_DATE).
Enter the Shift unit in I_WEEKDAY. If value is „1‟ then it will show Monday of the Next week and if any other number then it will show the according day.
Enter, if required, Number of weeks shift in I_NUMBER_OF_WEEKDAYS.
If you want to stay in the current month only then only make I_FLG_STAY_IN_MONTH = X else leave it blank.
To understand this FM let us take following example:
Example 1:
I_DATE = 09/06/2010
I_WEEKDAY = 1
I_NUMBER_OF_WEEKDAYS = 0
I_FLG_STAY_IN_MONTH = „„
If I_WEEKDAYS = 1 & I_NUMBER_OF_WEEKDAYS = n, where n is any whole number, then it will always show nth week‟s Monday irrespective of date.
Example 2:
I_DATE = 09/06/2010
I_WEEKDAY = 2
I_NUMBER_OF_WEEKDAYS = 0
I_FLG_STAY_IN_MONTH = „„
If I_WEEKDAY > 1 & I_NUMBER_OF_WEEKDAYS = n, where n is any whole number, then it will show nth week date based on I-WEEKDAY value.
If entered date is Monday, I_NUMBER_OF_WEEKDAYS = 0 and I_WEEKDAY = 2 then it will show the same week Tuesday i.e. 09/07/2010 in our example.
If entered date is Monday, I_NUMBER_OF_WEEKDAYS = 3 and I_WEEKDAY = 2 then it will show after 2 week‟s Tuesday i.e. 09/20/2010 in our example.
Like this few more combinations can be made as per the requirements. WEEK_GET_FIRST_DAY: To get the First date of the Calendar Week
Hi there, thanks for the codes, really appreciated it.
ReplyDeleteInstead of using BILLING DATE, I am using 0DOC_DATE, would that makes any difference?
and also, when I tried the code, for the part:
Data:
YEAR (4) TYPE N,
DATE1 LIKE SY-DATUM,
DATE2 LIKE SY-DATUM,
DAYS TYPE P,
WEEKS (2) TYPE N,
NUMBER (2) TYPE N,
FISCV (2) TYPE C.
It actually prompted Syntax error:-
E:"(4)" is not expected
Infact, all the (NUMBER) is highlighted in red colour, what could possibly be wrong?
Kindly Share your thoughts, thanks!
Hi there again, I solved the syntax issue, but there's one other problem, it seems like the Function module: '/SDF/CMO_DATETIME_DIFFERENCE' doesnt exist in the BW 7 I am using, do you have any alternative Function module that have the same function?
ReplyDeleteMany thanks