on 10-26-2015 4:06 PM
Hi,
I have got a formula in BEx Query, it has an if else condition to display the value. the first part is when if condition is true, display the value in a keyfigure else display fixed value 100%.
The displaying keyfigure is created in back end, it is defined with % as Fixed unit of measure. So, the first part of the condition is working fine, the else part I am unable to display the constant value as '100%', I am wondering how can define fixed unit of measure in BEx formula for a constant value?
Do you know, how can I display it?
Thanks
Sreekanth
Hi, Actually, I am not expecting a constant value in that formula, as I said earlier, I have a formula defined with if else condition, the else part is to get 100 %. I can put the formula to display 100 but not 100%, I am wondering is there any I can show 100% in the report output.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi sreekanth,
can you post your bex formula ( if else) here? or may be an example?
I can put the formula to display 100 but not 100%, I am wondering is there any I can show 100% in the report output.
sorry, I didn't get you here, do you mean you are not getting value as 100% ?
If I understand your question correctly,
if <conition true> result KF1 else <condition false> result should be 100% ?
create new formula ex: KC1 (as mentioned in previous post ) for constant value , then your if else formula should be like below
<condition true> * KF1 + <condition false> * KC1
KC1 gives the constant value as 100%
Message was edited by: Jyothi velpula
also, there is another problem with this formula,
Interest rate,
if (FV_Company Code = XXXX) then ZInterest
else,
100%.
endif.
In this, FV_company code is a formula variable created on Company code char as replacement process type, if company code char is not in the drill down of the report, then the formula is not working, it is displaying as X.
Users do not want company code char in the default drill down, it has to be in free characteristcs section only, What should I do to make the formula work, though the company code char is not in drill down.
Hi,
in that case, create a dummy key figure in target --> then create a selection /restricted key figure (ex: RK_compcode) with company code and that dummy key figure.
now use this newly created selection/restricted key figure in your formula (if RK_compcode = XXXX)
Psnote: this will still bring X values if the company code is in free char.
this formula works only when user drag and drop company code in rows.
Then it would not help anyway to me, I am looking for an option to use a formula with replacement patch variable on a Characteristic company code, but not having that char in the report drill down. Let me know if that can be achieved by anyway.
also what you said to get 100% value in the formula would not work, I have created a dummy keyfigure with a formula - 0 / 100 , it gives me 100% for that column, but when I used in the main formula, it is still showing as 100. So there is no use of doing it.
Otherwise, I will convince the user to adjust the worksheet with this new column in spread sheet.
Hi,
Did you already get the 100% part? If not, you use this formula in your ELSE statement..
1 %A 1
It will give you the result of "100%"..
About your 2nd problem, is your company code a NUMBER or LETTERS?
If it's a number, we can use a replacement path to make it as number then we can do an IF statement..Else, you put your company code in the drill down but in display just choose HIDE..
Regards,
Loed
I am able to resolve the 2nd problem. I have kept the char in drill down but selected 'No display option'. It is working fine. Thank you.
The first issue, getting 100%, even with your formula also, it did not work. I can display the keyfigure as 100% in a new column with the formula 1 %A 1. But when I used that keyfigure in another formula, it is still taking only value of 100, not the 100%. Because % is a dimension of a keyfigure, I think I can only get it from the back end, it seems.
May be, I have to write a customer exit to read as Virtual keyfigure value for it.
Hi All,
any help on % dimension issue. With this reason, the formula is giving an error message that, it is not correctly defined. However the report output is fine. if there is no solution to it, then I have got following 2 options,
1. Suppressing the error message, assuming that, the report would still give the correct values.
2. Creating virtual keyfigure in the cube with % as fixed dimension, populate that using Customer Exit in reporting.
Please share your ideas, if there is any other way to handle this situation. thank you in advance.
Sreekanth,
Ok Loed, here is the details of the issue,
formula for net interest before, if Net Interest actual = 0 then take Net interest Budget else
if net Interest actual <> 0 then take Net interest actual
Net interest Actual and Net Interest Budget are 2 standard keyfigures in the back end with unit of measure as %. so the values be 50% or 60% etc..
It was working fine before, the problem started when I changed the formula definition of it,
now this formula is changed as, if net Interest actual = 0, and Net Interest Budget = 0 then take '100 ' else if net interest actual <> 0 then net interest actual.
With this change, there is a chance that, Net Interest can get 100 as value which is a number without a dimension, whatever the ways I tried to display the 100% in this formula it did not work. I tried all the suggestions above in this thread, I am able to display 100% by creating a new column but when I used the new column in this formula, it is not taking % from there, because it is not a unit of measure dimension associated with it.
So that, the formula definition is giving warning message, it is not an error.
the error is , Element Net Interest is not correctly defined, please check the query definition,
the definition warning says, one of the if condition result is giving % value value and other one is giving just the number, which is 100.
Option I am going with is, assuming that it is a warning message, suppressed it in RSRT, the query is executing fine. also checked the values, they are good.
Please let me know, if that works fine... also thank you very much for your interest in assisting on this issue.
Thanks
Sreekanth
Yes Loed, I have tried it, new column can display the % associated with number, but if it is used in other formula it would result in 100,
finally we convinced the user to display 100 for some records and some would display as 100% format, it is fine now.
Thanks you very much all the help you provided, highly appreciated your effort.
Hello Sreekanth,
pls share ur formula.
yes , in some cases else part will not work.So instead of else part u can make it to if condition in formula.
ex. suppose if your formula is like: PO = 100 then 1 else 0.
so instead of using PO ='100' * 1 + 0
u can use (PO = '100') *1 + (PO <> '100') *0
check this workaround .
Regards,
Hitesh Gavande
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
You can find a very useful doc (for the first way, BEx) following this link https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sapportals.km.docs/documents/a1-8-4/how to report data in alternate units of measure
Otherwise, if for you it's not necessary to have the indication of the UoM with your amount
On the other hand, you can use the update rules to convert your UoM when you load data in the infoprovider !
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Sreekanth,
you can create a new formula for constant value like below
- (0 % 100)
this will give you the constant value as 100% for each row. In the else place, use this column to populate.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.