Skip to Content

This is a very simple document to demonstrate how to use characteristics in a BEx Query as a part of a calculation.

Example: There are two date: Start Date & End Date (characteristics) in the query and we want to use the difference [End Date – Start Date] in a calculation.

So how to we use a data or any other characteristic in a Formula?

I will explain the same step by step with screenshots.

Here I take an example as a Date.

Step 1: Create a new formula.

Step 1.PNG

Step 2: Create a new Formula Variable.

Step 2.PNG

Step 3: Change the Processing Type to “Replacement Path” and select the characteristic to the characteristic which we need as a key figure.

Note: Make sure the characteristic or the part of the characteristic which you want to use as a key figure is numeric.

Step 3.PNG

Step 4: Go to the “Replacement Path” tab and Select the “Replace with” value as “Key”.

Here in the “Offset Setting”, you can specify the offset (position of the character) and the “Offset Length” from where you want to use the value as a key figure.

Example: Date is “20120415” (YYYYMMDD) and let’s say you want to use the “Day” part of the date as a key figure. I.e. 15. So you set the Offset start as 7 and Offset Length as 2. If you to use the whole date as keyfigure, You can use “Offset Start” as 0 and Offset length as 8.

Step 4.PNG or Step 6.PNG


Step 5: In the formula created in Step 1, insert the formula variable which we created and we have the characteristic ready to be used as a key figure or for calculations.

Step 5.PNG

We can use the same procedure show above to use any characteristic as a key figure.

To report this post you need to login first.

20 Comments

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

  1. Namita Agrawal

    Thank You so much for the demonstration.

    This is the exact scenario where I get stuck mostly. 🙁

    And seriously. this helped me a lot, to get rid of unwanted error messages ❗

    Thank You.

    Namita Agrawal

    (0) 
  2. Priyanka Jain

    Hi,

    The document is really good. And has helped in solving my problem till some extent.

    But I am facing one issue. I did as its mentioned in the document. I have converted a Date type characteristic IO into Key fig and have tried to display its value in column section. The issue is that the date value is not getting displayed instead its showing “X” this as output.

    Please help me on this.

    Thanks

    Priyanka

    (0) 
    1. Anshu Lilhori

      Hi,

      In currency unit/tab choose dimension as Date and also make sure to keep date in rows

      otherwise formula variable with replacement doesn’t work.

      Hope this helps.

      Regards,

      AL

      (0) 
  3. Priyanka Jain

    Hi Anshu,

    Thanks a lot for the solution.

    It worked 🙂 🙂

    But isnt it possible that without adding the date field in Rows or in Free char the value should b displayed by formula variable?

    Because if i am adding this field in rows .. the value is getting displayed twice. Thou to avoid this i have made the value in Rows hidden.

    Thanks,

    Priyanka

    (0) 
  4. Kamal Mehta

    Thanks for sharing.

    2 things to be taken care :

    1. Processing by “Replacement Path “

    2. Dimension as “Date” in Unit/conversion tab .

    Thanks

    (0) 
  5. G. Bot

    Thank you, Anshu,

    This is exactly what I need to display the last order per customer in my report.

    Do you know if this query also works when using it as a WEBi datasource using BICS connection? I believe conditions are not supported, but could be mistaken.

    Regards,

    Gerard

    (0) 
    1. Anshu Lilhori

      Your knowledge is correct–We can only convert dates,Characteristic which have numeric values.

      Any characteristic which has string in it,that doesn’t work.

      Regards,
      AL

      (0) 
  6. Daniel Ray

    hi,

    where is the characteristic being selected, you have created a new formula in the column section, and created a variable in the available operands section, by right click and new variable….after that you have selected replace variable with infobject and replace with Key. But my question is in which step are you selecting the characteristic date field or what ever for this process. I am unable to do it, using your steps in my system. Please help.

    (0) 
  7. Umar Nayab Umar

    Hi Anshu,

    I would like to ask, now as you have converted characteristic into key figure with a condition that he characteristic should be numeric like in oyur case ‘DATE’.

    What if, we want to perform calculation on non numeric characteristics…example, I want a count on Employee Name field so that if is place it against let’s say branch, the output should give me employee count against the branch.

    Hoping for your reply, as its a loooong back post.

    Regards,

    Umar Nayab.

    (0) 

Leave a Reply