Skip to Content
Author's profile photo Anshu Lilhori

Formula Variable Demystification

Scenario

A year and a half ago i came across a post in SCN where the requirement was to get the difference of days based on the user input interval on the variable selection screen.

Let’s say for Eg: if user input is 01.03.2012 to 13.03.2012 so the difference would be 13 days.

Most of the people suggested tryIing with code in cmod and i thought of exploring the options in BEx query designer itself.So after searching for a while i stumble across this Difference radio button in formula variable.I tried my hands out with it and achieved the desired result.

So now i will be sharing the same approach with detailed navigation on the same.

Step 1 –Create a new formula —Under that you have folder named as formula variable–Right click on it –New variable—Give the details as mentioned in below screenshot.

I have created on Actual GI date you can choose any other time characteristic based on your requirement.

FV1.JPG

Step 2Hit the replacement path tab and give the details as per the below screenshot.

Give the technical name of variable created on date (User input variable) of type interval.

In the Use interval section choose Difference radio button

fv2.JPG

Step 3—In the details and default values tab nothing needs to be selected.

Step 4—In the currency/Unit tab choose dimension as Number.

fv3.JPG

Step 5—-Save this formula variable and Drag and drop into formula.Refer the screenshot.

fv4.JPG

Save the query and now let’s execute the query and check the results.

Variable screen:

fv5.JPG

Output:

fv6.JPG

So as you can see that based on the input in variable selection screen we got the difference of days.

Hope this might be helpful and useful.

Reference:

http://scn.sap.com/message/13162504

Assigned Tags

      66 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Prashanth konduru
      Prashanth konduru

      Good one ... Thanks for Sharing ...

      Cheers 🙂

      KP

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks KP..

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Very nice information Anshu. Simple and fast. Thanks for sharing.

      Cheers

      Yasemin..

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks yasemin for liking the post.

      Regards,

      AL

      Author's profile photo CH Raman
      CH Raman

      Useful article and helpful one. Thanks for making and sharing Anshu.

      Thanks

      Raman

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Raman for your valuable comments.You always like the contents posted across different forums and give the feedbacks as well.

      Really remarkable.

      Regards,

      AL

      Author's profile photo CH Raman
      CH Raman

      Thanks for your comments Anshu,

      Author's profile photo Former Member
      Former Member

      Nice Document

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thnx sushant...for rating the blog..

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Thanks for your sharing Anshu.. Good Document.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thnx venu..

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Very Informative...Many Thanks.

      Best Regards,

      Naresh K.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thnaks Naresh..

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu, your post is very helpful. Thanks!

      Regards

      Venkat...

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thnx a lot venky..

      Regards,

      AL

      Author's profile photo Umashankar Poojar
      Umashankar Poojar

      Very Informative and useful for each SAP BI consultant in their career.

      Must know feature, thanks for sharing.

      Really appreciate your valuable time and effort to share this.

      Cheers!

      Umashankar

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for your kind words Umashankar.

      Really appreciate your feedback.

      Regards,

      AL

      Author's profile photo Kamal Mehta
      Kamal Mehta

      Short and crispy .

      Nice one.

      Thanks

      Author's profile photo Former Member
      Former Member

      Thanks for sharing this Anshu...Cause I confront the same scenario recently, and done using CMOD.

      Very Helpful document.

      Thanks,

      Karan Lokesh.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Karan..Next time if you get the same requirement then you may try this approach..

      Hassle free without code.. 😉

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Good One Anshu....Never thought of this trick to get the difference, this is cool....One way which I had tried to create 2 separate formula variables for the dates and then calculating the difference in a CKF....

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for the feedback..Yes we can do the way you suggested as well..

      Regards,

      AL

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Nice information and really very short.

      Regards,

      Satendra Mishra

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for the feedback satendra..

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Good one.. Its very useful... But i have little but confusion what is difference between in Interval Form Value, To value.. It would be great if you will help me out to resolve my problem...

      Thanks for sharing..:)

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for your feedback..Regarding your confusion..From value read the from value of the user input variable type interval..And to value will read the to value.

      So for eg:If user enters 01.01.2003 - 31.01.2003

      From value holds--01.01.2003

      To value holds--31.01.2003

      Hope it clears your confusion.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu..

      Thanks a ton...Its now clear...

      Thanks,

      Chandresh

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Crystal clear explanation.

      Regards,

      S.Venkatesh Babu

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for your crystal clear feedback.. 🙂

      Regards,

      AL

      Author's profile photo Sandeep a
      Sandeep a

      Nice information thanks for share.. 🙂

      Regards

      Sandeep

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for the feedback sandeep.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Nice approach, will use same in upcoming requirement 🙂

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks KD..

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hey Anshul,

      Thanks for sharing this.

      Indeed a useful information 🙂

      Thanks,

      Priyanka

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Priyanka..

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      in short time great achievement & clear explanation... 🙂

      Thanks for sharing.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Mithun..really appreciate your comments.

      Regards,

      AL

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      Nice finding Anshu.. 🙂 .Sometimes simple work arounds work fantastic..Great job

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks suman for your wonderful comments..You made my day.. 🙂

      Regards,

      AL

      Author's profile photo Ravi Chandra
      Ravi Chandra

      Good Doc Anshu....

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Ravi..for your feedback..

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi,

      The above details will definitely helpful for my scenario also.

      Thanks for the nice document.

      Thanks & Regards,

      Chandra Sekhar.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Sekhar for your valuable feedback.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Very helpful Anshu,

      Thanks for Sharing.

      Anil.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Anil for your valuable feedback..Much appreciated.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Its really helpful for a BI Consultant, Most of times we will Face this kind of  scenarios in every requirement .

      Thanks For Sharing 😉 😉 😉 .

      Regards,
      Giri

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Hi Giri,

      Thanks for your wonderful comments..I completely agree with you and that is the reason i thought of presenting it.For all such requirements we tend to write code and indeed which is not required.

      I will share new scenario very soon.Stay tuned.. 🙂

      Regards,

      AL

      Author's profile photo Harshawardhan Ghatge
      Harshawardhan Ghatge

      Nicely Documented & well explained Anshu..

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Harsha...for your valuable comments.Much appreciated.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      HI Anshu,

      NIce Document .....

      Regards

      Subbarao

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Hi Subbarao.

      Thanks for your feedback.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Thanks for the document. Really it'll be helpful.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Hi Chandra,

      Thanks for your feedback.

      Regards,

      AL

      Author's profile photo Ashok Babu Kumili
      Ashok Babu Kumili

      Hello Anshu Lilhori,

      Great read.. Indeed, Very helpful...

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Ashok Babu for your feedback..Much appreciated.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Good one,

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for your feedback.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Very informative document,

      Regards,

      J.Sakthikumar

      Author's profile photo Vijay Chandra.R
      Vijay Chandra.R

      Dear Anshu,

      Well written document.Thanks for sharing.

      Regards, Vijay

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks vijay and sakthikumar for your comments.Much appreciated.

      Regards,

      AL

      Author's profile photo Wasem Hassan
      Wasem Hassan

      Hi Anshu,

      Good one doc.

      Thanks for sharing 🙂

      Keep it up.. 🙂 🙂

      Reagards

      Waseem

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thank you wasem.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Thanks for sharing this document.

      I have one quick qtn, You have given above scenario where user input is intervals ,If user has to give single date value and other value is already part of infoprovider , how it will be considered for calculating the dates difference ?

      I am assuming Formula variable with manual input , however we cannot change it as Date, i mean in currency /unit tab. Can you add your comments on it .

      Thanks in advance.

      Reg,

      Medha

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Medha,

      This is very much possible just by altering few things in the steps mentioned in the blog.

      In step 2 choose the radio button from value instead of difference.

      In step 4 choose dimension as date under currency/unit tab.


      This way you will get the date entered by user.


      Regards,

      AL

      Author's profile photo Hello PAMARTHI
      Hello PAMARTHI

      Hi Anshu,

      Document was very nice and helpful..

      Thanks & Regards..

      P Sriram.

      Author's profile photo ARPAN GHOSH
      ARPAN GHOSH

      Hi,

      I am facing problem in the result of the difference in days.

      Our requirement is to get difference no. of days where posting date - clearing date & if the document is not cleared then calculate as report run date i.e. posting date - report run date. I am getting the figures correctly but the total is coming blank. I cannot use SUM from properties because I require the total to be calculated automatically. Please HELP!!