How to use Replacement Path Variables to perform Date Calculations
A Step-by-Step guide
Have you ever wanted to perform calculations using dates defined as characteristics but have never worked out how it can be done? Replacement Path Variables are the key.
Using Replacement Path in your Text and Formula variables, replace values held in a characteristic. The Characteristic value variables are replaced by the results of a query at run-time.
The steps detailed below show a technique to enable a BEx query user to determine the number of days between two dates.
Scenario:
The group HR administrator wants a detailed line item report that lists all employee absences in a given period. The report is to show the employee number, the absence start date, together with the end date of the absence and show the number of calendar days the employee was absent.
The good thing about using this technique is that no redesign work is needed from your technical BW team, no ABAP is involved and best of all, it quick and easy.
Solution:
For this example I created an ODS object called Absence that holds basic employee information along with individual absence record data.
Follow the steps below:
- Open the BEx Query Designer and create a new report on your chosen InfoProvider.
- Drag the Employee, Valid from and Valid to characteristics into the Rows section of the screen. If needed, apply data selection restrictions to the characteristics as shown in Figure 1.
- Right click on the Key Figures structure and select New Formula (Figure 1).
Figure 1 - In the new formula window right click on Formula Variable and choose New Variable ( Figure 2 ).
Figure 2 - The Variables Wizard will launch and will require you to specify the variable details.
( Click the NEXT button if the Introduction screen appears ) - Enter the variable details on the General Information as shown in Figure 3 .
Enter the Variable Name , Description and select Replacement Path in the Processing by field.
Click the Next Button.
Figure 3 - In the Characteristic screen (Figure 4) select the date characteristic that represents the first date to use in the calculation (From Date).
Click the Next Button.
Figure 4 - In the Replacement Path screen (Figure 5) select Key in the Replace Variable with field. Leave all the other options as they are (The offset values will be set automatically).
Click the Next Button.
Figure 5 - In the Currencies and Units screen (Figure 6) select Date as the Dimension ID.
Click the Next button.
Figure 6 - The Save Variable screen (Figure 7) displays a summary of the new variable.
Click on the Finish button to save the variable.
Figure 7 - Repeat steps 4 to 11 to create a second variable for the second date to be used in the calculation. In the example shown, the characteristic 0DATETO is used to create the variable ABSEND (Absence End Date).
Define the Calculation
We can now use our two new replacement variables to define our new calculated key figure that generates the number of absence days for each record.
- You will now be back at the New Formula screen (Figure 8). Drag and drop the two new variables into the formula section of the screen and insert the subtract sign (“-“) between the two.
- Give the new formula a description and click the formula syntax check button
to ensure the formula is valid.
Figure 8 - The new calculated key figure will now show in the columns section of the BEx query designer (Figure 9).
Figure 9 - Save the query and execute it.
In the example shown the Number of Calendar Days Absent is calculated correctly. See the table of results below.
| Employee | Valid From | Valid To | Number of Calendar Days Absent |
|---|---|---|---|
| 50000001 | 17/04/2004 | 21/04/2004 | 4 |
| 50000002 | 16/07/2004 | 29/09/2004 | 13 |
| 50000003 | 07/01/2004 | 09/02/2004 | 33 |
| 50000004 | 04/08/2004 | 05/08/2004 | 1 |