How to derive Calmonth & Calyear from Calday easily in BEx
Introduction
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.
Conclusion :
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.
Nice Work.
Regards,
Sushant
Hi Sushant,
Thanks for the feedback 🙂
Regards,
Suman
Hello Suman,
Good and concise explanation of 'Replacement path'. Keep it up.
regrads,
Anand Kumar
Glad to see your comments Mr.Anand 🙂
Good presentation and very well drafted about replacement path. Thanks for sharing suman.
Thank you Raman for your valuable feedback 🙂 🙂 U r Welcome..
Nice Explanation Suman. Thanks for sharing 🙂 🙂
Thank You Akash for your cheerful comments 🙂 🙂 🙂
Nice Document Suman..... 🙂
Regards,
SM
Thank you Mishra for feedback 🙂
Hi Suman,
Very good informative and motivated doc. It is really useful in so many scenarios.
Thanks for sharing Boss. You are rocking. 🙂
Regards,
Krishna Chaitanya.
Made me to smile instantly 🙂 😆 😆 . Amazing comments. You say it is motivating as well. Thrilled to know that..
Regards,
Suman
Yessssssss...It doesnt matter who hurt you or broke you down. What matters is who made you smile again. 🙂 😉 right?
Of course dude. Thanks for reading my blog 🙂
Nice Document ,Thanks for sharing
regards
Sandeep
Thank you Sandeep 🙂 for your feed back.
very well explained...
Thanks for sharing.. 🙂
Good one Suman...:)
Thank you Sandeep Chauhan, Prerna Rathore and Naveen Choudhary for your cheerful comments. 🙂 🙂
Hi suman,
very well explained use of replacement path..thanks for sharing 🙂 .
Thank you Ganesh Bothe for your comments. 🙂
nice work and clearly explained Suman!
Thank you Martin for nice feedback 🙂 🙂 My intention is to make it crisp and clear. 🙂
I missed this one.
Useful and Thanks for sharing the document.Rated the document.
Cheers
KP 🙂
Thank you Prashanth for wonderful comments 🙂
Nice and informative document.
Regards,
Shyamala.V
Thank you Shyamala 🙂
Off the record. Congrats suman becoming Diamond level. Its Wonderful journey along with you. Once again Congrats for you support to SCN.
I am delighted today to see Diamond(the ultimate level in SCN) tagged to my name. This is a really long journey. Thanks for your encouragement 🙂
Regards,
Suman
Congrats dude 🙂
This is for you.
http://hdwallpaper.freehdw.com/0001/nature-landscapes_other_flower-bouquet_3630.jpg
Expecting Party 😉
Hey Krishna,
This is really amazing gesture. 🙂 Thanks a lot for it.
Regards,
Suman
Congrats Suman ..... U deserve it 🙂
Thanks a lot for your warm wishes Shyamala Kalluri 🙂 🙂 🙂
Thats really informative Suman, Thanks a lot for sharing the info.
You have very well expalined it..!! 🙂
Regards,
Priyanka
Thanks for the cheerful comments and the valuable feedback Priyanka Jain 🙂 🙂
Awesome piece of doc.
Thank you for your wonderful review Arshiyan - 🙂 🙂
Excellent Document,
Thanx for sharing Suman!
Thank you sudarshan reddy for your valuable feedback 🙂 🙂
Hi Suman,
Nice exploration and explaination, as always 🙂
Thanks for sharing.
Regards,
Mohammed.
Amazed by your words Naveed 🙂 Thank you..
Hi Suman,
Good document.. its very helpful
Thanks,
Chandresh Patel
Thank you Patel
Exceptional!!!! Please post RRI related also if possible!!!!
Br,
Ravi Kiran.
Thanks a lot Ravi for your cheerful compliments.
Simple and superb suman.
This document clarified lot of my doughts in using replacement path
1) How to create replacement path using variable.
2) How can we use "FROM & TO" options in replacement path
Nice thing here is, as a finishing touch you have used OFFSET very effectively in end of this document. gr8. Thanks.
Best Regards,
Venu Gopal
I am really delighted to read your line by line review. Fantastic kick to me 🙂 🙂
Thanks a lot Venu.
Thank You Very much.... 🙂
Welcome bl vijaykumar 🙂
Hello Sumanda,
Very nice KT. Please post a few more dada..
regards,
Aparajit
Hi Aparajit Banik ,
Thank you for your feedback 🙂 BTW, my name is SUMAN
This is a very good document and it proves we can derive calmonth , calyear without using customer exit or abap code.Many thanks.
Thanks Saikat Pal for your amazing rating 🙂 🙂 . Yes, Cust exits can be avoided if you are good in replacement paths and applying offsets.
you are an expert in bex. Excellent job. I dint know we can play around with this without abap code. wiz kid 😀
Hi Daniel Ray ,
Thanks for the appreciation 🙂 🙂 . Yep, Replacement path is the smart toy in bex.
Regards,
Suman
HI Suman,
Wonderful document never seen before.. 😯 . Clearly explained with step by step navigation. 🙂 🙂
Hi Praveen Raj ,
Thank you for the wonderful comments 🙂 Great feedback!!
Regards,
Suman
Suman! Great article! Thank you for posting it. Appreciate it! Not only did I learn about deriving 0calmonth from 0calday...I learned about you can play around with the system.
Regards,
Meghana
Hi Meghana Kuppa ,
Thanks for your noble words. 🙂 Thrilled by your last comment 🙂 🙂 .
Regards,
Suman
Hi Praveen Raj ,
It should work. Give a try..
Regards,
Suman
Good job!! 🙂 I am happy that my blog helped you.
Hi Suman,
One more very nice document from you. Shows great about how we can play around in bex. Thanks for sharing. 🙂
Hi Yasemin ULUTURK ,
Thanks a lot for your splendid comments 🙂 . We can play around with replacement path in Bex.
Regards,
Suman
Hi Suman,
Good & Informative document, Cleared my doubts regarding the Offset.
Thank you very much.
Regards,
Anil.
Thank you Anil Kuma for your wonderful comments. Glad to know your doubts have been clarified. 🙂
Good information ...
Hi Suman,
Got to see your blog post today, I must say thhis is excellent way to avoid Custmer Exits.
Very well explained. looking forward for some more useful information from you.
Regards,
Vikrant
Glad to receive amazing feedback from you Vikrant Soni 🙂 🙂 . User exits have to be avoided wherever it is possible by using replacement path and offsets.
Thank you for your feedback Laxmikanta Verma 🙂 🙂
Best Regards,
Suman
Hi,
This is awesome, wonderful way to ignore cmod. Can you share some more thoughts on LYMTD & LYYTD using Replacement Path.
Keep rocking, people like you are always helpful to step us further in BW!!! 😎
SumanT