How to derive Calmonth & Calyear from Calday easily in BEx
This may be obvious topic in BEx. I believe “Replacement Path” processing type is the most smartest one which can avoid many User Exit variables, for which we break our heads to write Code to do our Trend Analysis. Hope you guys got now, what am I going to demonstrate in this simple blog.
Scenarios covered are :
- Dummy KF to enable Selection in variable Screen
- Calmonth from Calday
- Calmonth from Calday (Range)
- Calyear from Calday and Calmonth
The basic pre-requisite to derive above scenarios is a Calday Variable. This can be a “Single Value” or “Interval” as well.
Imagine below in my screen shots
Calday = Invoice Clearing Date
Calmonth = Invoice Clearing Month
1. Dummy KF to enable Selection in Variable screen
Why a dummy KF is required?
Let’s suppose we have a Calday variable in Filter or in Default Values pane in our query designer. This means, we are restricting globally our time period as per the variable entry, by which we cannot calculate for past or future periods. To overcome this, we have to bring our variable to KF pane by restricting to a dummy KF and hide the KF. Then this variable becomes local and appears on the variable screen which is our desire.
2. Calmonth from Calday
Suppose User enters Calday : 01.11.2013, then 11.2013 can be picked by creating a Replacement Path variable under Calmonth like below.
The offset length: 6 is the Key Parameter to pick up 11.2013 from 01.11.2013. This is some thing like we are considering only 6 Characters out of 8 characters through ABAP Code.
3. Calmonth from Calday (Range)
Suppose we have a Calday (Interval) : 01.07.2013 to 10.08.2013. Please observe To Date is a different month. Now we want to see our KFs as per From and To Dates.
I mean one of your KF has to show data for 07.2013 by based on From date and your Other KF has to show data for 05.2013 by based on To date.
Create two variables like Calmonth FROM and Calmonth TO by replacement path of Calday (Interval)Variable like below.
Finally you have to restrict your KFs with respective Calmonth FROM and Calmonth TO and apply Offset-3 to get 05.2013 like below. You can apply any Offset value for both variables. That’s How we can play with these derived variables.
After executing the report, you will get the result like below.
To cross check whether our two variables have really replaced our Interval variable or not, I have drill downed with Invoice Clearing Month(Calmonth). It shows correct months for the corresponding KFs, by which we can confirm that our mechanism works.
4. Calyear from Calday and Calmonth
Suppose we have Calmonth and Calday variables, we can derive Calyear by creating two separate variables by considering offset parameters like below.
We can do many of our Trend Analysis by just using “Replacement Path” and applying Offsets for past and future periods. This will reduce unnecessary time spending on User exits. We should go with User exits unless they are really required.