Inorder to restrict the Date based on some characteristic, we have to use Date as a keyfigure.
For, example consider the below scenario.
Status = 001 = Inprocess.
Status = 002 = Closed.
We want to restrict date with status “001” and “002”.
For example we restrict amount with company code XYZ to get amount related to company code XYZ , likewise over here we want to restrict date with status = 001 to get date on which the ticket was Inprocess and with status = 002 to get the date on with the ticket was closed .
Inorder to restrict date, we have to convert Date into keyfigure. We can convert a characteristic into a keyfigure by the below mentioned steps:
- Create CKF at Infoprovider level, to later use it in RKF. Go to KEYFIGURE folder at Infoprovider level, and create New Formula. Under formula variable create new formula variable.
2. Select Replacement Path processing by and choose your date characteristic as reference char.
3. On replacement path tab, select replace variable with infoobject and replace with key.
4. On Currency/Unit tab, choose date dimension.
5. Finally use this variable into global CKF and then use it in RKF.
There are two date: Start Date & End Date (characteristics) in the query and we want to use the difference [End Date – Start Date] in a calculation.
So how to we use a data or any other characteristic in a Formula?
I will explain the same step by step with screenshots.
Here I take an example as a Date.
Step 3: Change the Processing Type to “Replacement Path” and select the characteristic to the characteristic which we need as a key figure.
Note: Make sure the characteristic or the part of the characteristic which you want to use as a key figure is numeric.
Step 4: Go to the “Replacement Path” tab and Select the “Replace with” value as “Key”.
Here in the “Offset Setting”, you can specify the offset (position of the character) and the “Offset Length” from where you want to use the value as a key figure.
Example: Date is “20120415” (YYYYMMDD) and let’s say you want to use the “Day” part of the date as a key figure. I.e. 15. So you set the Offset start as 7 and Offset Length as 2. If you to use the whole date as keyfigure, You can use “Offset Start” as 0 and Offset length as 8.
Step 5: In the formula created in Step 1, insert the formula variable which we created and we have the characteristic ready to be used as a key figure or for calculations.