Calculating number of working days between two dates(e.g.YTD- From 1st January to Current Day) and using it for calculations in queries is a very general case for reporting models.
This blog gives a solution for these type of calculations using factory calendars maintained in BW. To see the available factory calendars TFACD (Factory Calendar definitions) can be used Details of Factory Calendars can be found in TFACS.
There are several function modules using these factory calendars and helping for necessary calculations.(*) One of them is DATE_CONVERT_TO_FACTORYDATE. This function module returns the factory date for the date and factory calendar passed. Double use of this FM (one with start date and one with the end date) will give two numbers and the difference gives the total number of working days between. By using this FM number of working days between two dates can be easily determined.Once you have this function module, it becomes very easy to include this calculation in query level by creating a formula variable(customer exit) and using DATE_CONVERT_TO_FACTORYDATE with the desired time period dates.
As an example; the steps below describes the “number of worked days in a month till today” calculation using this strategy.
- Create a formula in query designer and double-click on it.
- Right Click on Formula Variables Folder in Available Operands Panel
- Create a formula variable “ZWRKDDYS”.
- Select “Customer Exit” for “Processing By” property.
- Go to Transaction CMOD and open project RSR0001
- Insert the following code to the project.
data: end_date type d,
prev_wrk_day type sy-datum,
start_date type d,
bgn_of_month type sy-datum,
num_of_days type d.
concatenate sy-datum+0(6) '01' into bgn_of_month.
prev_wrk_day = sy-datum - 1.
CALL FUNCTION 'DATE_CONVERT_TO_FACTORYDATE'
EXPORTING
CORRECT_OPTION = '-'
DATE = prev_wrk_day
FACTORY_CALENDAR_ID = '00'
IMPORTING
FACTORYDATE = end_date.
.
CALL FUNCTION 'DATE_CONVERT_TO_FACTORYDATE'
EXPORTING
CORRECT_OPTION = '+'
DATE = bgn_of_month
FACTORY_CALENDAR_ID = '00'
IMPORTING
FACTORYDATE = start_date.
.
num_of_days = end_date - start_date.
e_t_data-sign = 'I'.
e_t_data-opt = 'EQ'.
e_t_data-low = num_of_days.
append e_t_data to e_t_range.
endif.
- Save and Activate.
- In the formula the number of working days till today can be seen and used for other calculated key figures.
Assumptions:
There is only one factory calendar with id = ‘00’.
Today is not included in calculation.
Project “RSR0001” is already activated in CMOD.
* For all available function modules please check the link.
Baris Gunes is a BI consultant at Accenture Istanbul Office.
