Webi: Formatting issue with measure and converting dot to comma in variable results

[former_member555797]

capture1.png

capture2.png

Hi All, I have two questions, I have a bex query connected to webi.

1) I am using a standard Formula from query structure for webi. One of the measure is Var %, in webi its availabe as Var % and Var % Formatted value. I have to use the Var % Formatted value because Var % shows too many decimal places and its non formatable. If I use Formatted value object and try to create a conditional formatting than its giving wrong results. I am not sure why the format condition is not working on directly?

2) Instead I forund a workaround and made a variable for Var % =

=If (IsError([Sales P1]/[Sales P2]*100-100) ;0 ;([Sales P1]/[Sales P2]*100-100))

but the results are showing me values in . instead of , for example 9.40 instead of 9,40. Does anyone knows which formula to apply so that I can get comma instead of dot.

File attached

[amitrathi239]

What is the datatype of Formatted Value object in webi ?

[former_member555797]

Data type in bex is formula and in webi is measure. I hope this is what you would like to know

[amitrathi239]

in webi data type is string or number?

[former_member555797]

Its a string

[former_member555797]

Any solution for this, because for every (var %) i need to create variable in order for the condtional formatting to work .

[amitrathi239]

No need to create individual variables.use existing Var % formatted variable to convert in the number type to use in the conditional formatting.

V Rule=ToNumber(Trim(Replace(Replace([Var % formatted];",";".");"%";""));"#.#")

[former_member555797]

I have the following issue. I have Var % and var % formatted .

Issue : I can not use (var %) to two decimal places and if I use var % formatted than my conditional formatting is showing wrong colors.

[amitrathi239]

if you want to use any measure object in the calculation then it's data type should be number.

For your question to replace dot with comma you can use replace function in webi but after that you will not be abale to do the calculation on the variable.

Var % =Replace(If (IsError([Sales P1]/[Sales P2]*100-100) ;0 ;([Sales P1]/[Sales P2]*100-100));".";",")

[former_member555797]

Hi Amit, thanks. I am just new to webi so I am really thankfull to you for your suggestions. I found out that the measure was a string in webi (not sure its a bug) as i expected it to be a number. I converted the string in to number by using

=ToNumber([Var %(1)]) and this works but when i use this some of the cells which are empty without values are giving #error messages. Can you suggest a formula which can remove these error messages or replace #error with 0

[amitrathi239]

=if(iserror(ToNumber([Var %(1)]))) then 0 else ToNumber([Var %(1)])

[former_member555797]

Thanks a lot, would you recomend any information source where i can learn more about functions in webi

[amitrathi239]

check functional help guide.

https://help.sap.com/doc/businessobject_product_guides_sbo42_en_sbo42sp2_ffc_user_guide_en_pdf/4.2.2...

Apart from this i would suggest to close the question by selecting appropriate answer once your query is resolved.

[former_member555797]

Hi Amit, I will close it. One thing i am still finding an issue:

Value A is showing as a number in and KPI is showing as a string in webi. The result output is for example:

[Value A] = 10,000.23 and Tonumber([KPI]) = 12.6 % in webi

where ever in the BEx (RSRT) shows the opposite way

Value A = 10.000,23 and KPI = 12,6 %

Business wants the values to be in European format in dots instead of comma seperated and no decimals for Value A.

If i change the locale from England to (NL) then the KPI % values show error (#FOUT) while value is good.