Skip to Content

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

To report this post you need to login first.

66 Comments

You must be Logged on to comment or reply to a post.

    1. Anshu Lilhori 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

      (0) 
  1. 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

    (0) 
  2. S Kaur

    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….

    (0) 
  3. Chandresh Patel

    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..:)

    (0) 
    1. Anshu Lilhori 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

      (0) 
  4. SG Sg

    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

    (0) 
    1. Anshu Lilhori 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

      (0) 
  5. Medha Cindrilla

    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

    (0) 
    1. Anshu Lilhori 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

      (0) 
  6. 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!!

    (0) 

Leave a Reply