Skip to Content
Author's profile photo Jordan Lazarus Bodapaty

Convert a Characteristic into a Key Figure (BEx)

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.

Assigned Tags

      21 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jayasri Vinoth
      Jayasri Vinoth

      Hi,

          

      In the Replacement Tab, under replace with we have 5options. For more clearer explanation about this you can check out the below link:

      Replacement Path: Replacement with a Characteristic Value (SAP Library - BI Suite: Business Explo...  

      Author's profile photo Former Member
      Former Member

      Great article.

      I had to modify other things in order to have values displayed, but this helped me a lot!

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      Thank you very much..it really helped.

      Author's profile photo Former Member
      Former Member

      Thank you..

      its very useful.... 🙂

      Thanks

      Chandresh

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Anshu Lilhori
      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Anshu Lilhori
      Anshu Lilhori

      Hi,

      We would appreciate if you can open a new thread with proper issue so that other experts can also give input on the same.

      Regards,

      AL

      Author's profile photo Kamal Mehta
      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

      Author's profile photo KD Jain
      KD Jain

      Nice Document

      Author's profile photo Former Member
      Former Member

      Very useful blog. Learn something different.

      Thanks,

      B Devi

      Author's profile photo G. Bot
      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

      Author's profile photo Manna Das
      Manna Das

      one of the great stuff so far I have seen. thanks a ton for sharing this valuable doc.

      KR,

      Manna Das

      Author's profile photo Former Member
      Former Member

      Hi, If characteristics have text value then is it possible? As of my knowledge it works for only number and date fields.

      Thanks,

      Chandresh Patel

      Author's profile photo Anshu Lilhori
      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

      Author's profile photo Daniel Ray
      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.

      Author's profile photo Kishore Ch
      Kishore Ch

      hi Anshu,

      i tried this scenario  its working thanks for sharing ...............

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      Very helpful document

      Author's profile photo Maheshbabu Bodicherla
      Maheshbabu Bodicherla

      Hi ,

      thank you very much, your document is very helpful. However, i have a doubt, could you please help me.

      I have created a formula Variable with Replacement path , the reference characteristic is 0CALWEEK.

      Formula variable is being used in Calculated key figure (CKF), finally CKF has been used in the columns - Selection and restricted with current week - 4 .

      My query output is "X" . May i know the reason?

      Note: Query static filter is on Info provider and in that info provider,  column 0CALWEEK is not available but available at multiprovider level ( Underlying DSO & cubes)

       

      Regards,

      Mahesh