Skip to Content
Author's profile photo Suman Chakravarthy K

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 :

  1. Dummy KF to enable Selection in variable Screen
  2. Calmonth from Calday
  3. Calmonth from Calday (Range)
  4. 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.

Dummy KF.JPG

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.

Calmonth from Calday.JPG

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.

Calmonth from Calday Range.JPG

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.

Both KFs.JPG

After executing the report, you will get the result like below.

Result.JPG

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.

Result with drill.JPG

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.

Calyear from calmonth and Calday.JPG

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.

Assigned Tags

      69 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Nice Work.

      Regards,

      Sushant

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi Sushant,

      Thanks for the feedback 🙂

      Regards,

      Suman

      Author's profile photo Anand Kumar
      Anand Kumar

      Hello Suman,

      Good and concise explanation of  'Replacement path'. Keep it up.

      regrads,

      Anand Kumar

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Glad to see your comments Mr.Anand 🙂

      Author's profile photo Raman Korrapati
      Raman Korrapati

      Good presentation and very well drafted about replacement path. Thanks for sharing suman.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Raman for your valuable feedback 🙂 🙂 U r Welcome..

      Author's profile photo Former Member
      Former Member

      Nice Explanation Suman. Thanks for sharing 🙂 🙂

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank You Akash for your cheerful comments 🙂 🙂 🙂

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Nice Document Suman..... 🙂

      Regards,

      SM

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Mishra for feedback 🙂

      Author's profile photo Krishna Chaitanya
      Krishna Chaitanya

      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.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Made me to smile instantly 🙂 😆 😆 . Amazing comments. You say it is motivating as well. Thrilled to know that..

      Regards,

      Suman

      Author's profile photo Krishna Chaitanya
      Krishna Chaitanya

      Yessssssss...It doesnt matter who hurt you or broke you down.  What matters is who made you smile again.  🙂 😉   right?

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Of course dude. Thanks for reading my blog 🙂

      Author's profile photo Sandeep a
      Sandeep a

      Nice Document ,Thanks for sharing

      regards

      Sandeep

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Sandeep 🙂 for your feed back.

      Author's profile photo Former Member
      Former Member

      very well explained...

      Thanks for sharing.. 🙂

      Author's profile photo Former Member
      Former Member

      Good one Suman...:)

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Sandeep Chauhan, Prerna Rathore and Naveen Choudhary for your cheerful comments. 🙂 🙂

      Author's profile photo Ganesh Bothe
      Ganesh Bothe

      Hi suman,

      very well explained use of replacement path..thanks for sharing 🙂 .

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Ganesh Bothe for your comments. 🙂

      Author's profile photo Martin Grob
      Martin Grob

      nice work and clearly explained Suman!

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Martin for nice feedback 🙂 🙂 My intention is to make it crisp and clear. 🙂

      Author's profile photo Prashanth Konduru
      Prashanth Konduru

      I missed this one.

      Useful and Thanks for sharing the document.Rated the document.

      Cheers

      KP 🙂

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Prashanth for wonderful comments 🙂

      Author's profile photo Shyamala Valeti
      Shyamala Valeti

      Nice and informative document.

      Regards,

      Shyamala.V

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Shyamala 🙂

      Author's profile photo Raman Korrapati
      Raman Korrapati

      Off the record. Congrats suman becoming Diamond level. Its Wonderful journey along with you. Once again Congrats for you support to SCN.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      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

      Author's profile photo Krishna Chaitanya
      Krishna Chaitanya

      Congrats dude 🙂

      This is for you.

      http://hdwallpaper.freehdw.com/0001/nature-landscapes_other_flower-bouquet_3630.jpg

      Expecting Party 😉

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hey Krishna,

      This is really amazing gesture. 🙂 Thanks a lot for it.

      Regards,

      Suman

      Author's profile photo Shyamala Valeti
      Shyamala Valeti

      Congrats Suman ..... U deserve it 🙂

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thanks a lot for your warm wishes Shyamala Kalluri  🙂 🙂 🙂

      Author's profile photo Former Member
      Former Member

      Thats really informative Suman, Thanks a lot for sharing the info.

      You have very well expalined it..!! 🙂

      Regards,

      Priyanka

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thanks for the cheerful comments and the valuable feedback Priyanka Jain 🙂 🙂

      Author's profile photo Former Member
      Former Member

      Awesome piece of doc.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you for your wonderful review Arshiyan -  🙂 🙂

      Author's profile photo Former Member
      Former Member

      Excellent Document,

      Thanx for sharing Suman!

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you sudarshan reddy for your valuable feedback 🙂 🙂

      Author's profile photo Former Member
      Former Member

      Hi Suman,

      Nice exploration and explaination, as always 🙂

      Thanks for sharing.

      Regards,

      Mohammed.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Amazed by your words Naveed 🙂 Thank you..

      Author's profile photo Former Member
      Former Member

      Hi Suman,

      Good document.. its very helpful

      Thanks,

      Chandresh Patel

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Patel

      Author's profile photo Raki R
      Raki R

      Exceptional!!!! Please post RRI related also if possible!!!!

      Br,

      Ravi Kiran.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thanks a lot Ravi for your cheerful compliments.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      I am really delighted to read your line by line review. Fantastic kick to me 🙂 🙂

      Thanks a lot Venu.

      Author's profile photo Bayyavarap Lakshmi Vijaya kumar
      Bayyavarap Lakshmi Vijaya kumar

      Thank You Very much.... 🙂

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Welcome bl vijaykumar 🙂

      Author's profile photo Aparajit Banik
      Aparajit Banik

      Hello Sumanda,

      Very nice KT. Please post a few more dada..

      regards,
      Aparajit

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi Aparajit Banik ,

      Thank you for your feedback 🙂 BTW, my name is SUMAN

      Author's profile photo Former Member
      Former Member

      This is a very good document and it proves we can derive calmonth , calyear without using customer exit or abap code.Many thanks.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thanks Saikat Pal for your amazing rating 🙂 🙂 . Yes, Cust exits can be avoided if you are good in replacement paths and applying offsets.

      Author's profile photo Daniel Ray
      Daniel Ray

      you are an expert in bex. Excellent job. I dint know we can play around with this without abap code. wiz kid 😀

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi Daniel Ray ,

      Thanks for the appreciation 🙂 🙂 . Yep, Replacement path is the smart toy in bex.

      Regards,

      Suman

      Author's profile photo Former Member
      Former Member

      HI Suman,

      Wonderful document never seen before.. 😯 . Clearly explained with step by step navigation. 🙂 🙂

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi Praveen Raj ,

      Thank you for the wonderful comments 🙂 Great feedback!!

      Regards,

      Suman

      Author's profile photo Meghana Kuppa
      Meghana Kuppa

      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

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi Meghana Kuppa ,

      Thanks for your noble words. 🙂 Thrilled by your last comment 🙂 🙂 .

      Regards,

      Suman

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi Praveen Raj ,

      It should work. Give a try..

      Regards,

      Suman

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Good job!! 🙂 I am happy that my blog helped you.

      Author's profile photo Yasemin ULUTURK
      Yasemin ULUTURK

      Hi Suman,

      One more very nice document from you. Shows great about how we can play around in bex. Thanks for sharing. 🙂

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi Yasemin ULUTURK ,

      Thanks a lot for your splendid comments 🙂 . We can play around with replacement path in Bex.

      Regards,

      Suman

      Author's profile photo Former Member
      Former Member

      Hi Suman,

      Good & Informative document, Cleared my doubts regarding the Offset.

      Thank you very much.

      Regards,

      Anil.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Anil Kuma for your wonderful comments. Glad to know your doubts have been clarified. 🙂

      Author's profile photo Former Member
      Former Member

      Good information ...

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      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

      Author's profile photo Suman Thangadurai
      Suman Thangadurai

      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