Getting #Error when using ToNumber Function not working in BO WEBI XI3.1

[Former Member]

Hi All,

Please let me know if you have any resolutions to this issue:

I have 3 fields: ID,Contact no Amount and Unit. In which ID and Contact no is a dimension and Amount & Unit are measures.

I want to sum up the Amount based on the Unit, Unit will always have value 1,0,-1

Each ID can have multiple units and ID is not displayed in the report. Its only used in the SQL.

For ex: If Unit is 1, then Amount will be +Amount

If Unit is -1, then Amount will be -Amount

If Unit is 0, then Amount will be 0 and then I need to sum up all the amount foreach ID.

I am using below formula,

Var 1: Sum(ToNumber(Concatenation("-";[Amount])) Where ([Units] = -1) ForEach ([Id]))  - this is giving me #Error as I am not able to convert from string to Number.

Can you please provide your inputs ASAP.

Thanks for your help in advance.

[Former Member · Accepted solution]

Hi SS,

Leave the amount column with the base measure as =[Amount] and the write formatting rule or alerter with the below three conditions and format the number accordingly based on the condition specified for display purposes.

Rule 1:[Units]=1 within the formatting write a formula to ="+"+[Amount]

Rule 2:[Units]=-1 within the formatting write a formula to = "-"+[Amount]

Rule 3:[Units]=0 within the formatting write a formula to =0*[Amount]

When you are calculating the sum you can use the similar logic in and If clause and use as below and sum up all.

[Units]=1 then 1*[Amount]

[Units]=-1 then  -1*[Amount]

[Units]=0 then 0*[Amount]

Thanks

Mallik

[former_member188911]

The correct approach is to multiply by -1 rather than using ToNumber and concatenation of the minus sign

[Former Member]

Hi,

Thank you all for providing the solutions, its wokring, I used Amount*-1 and its working fine.

Thanks once again.

Regards

Sindhu

[jyothirmayee_s]

HI,

Why not create a seperate variable in Webi for Unit and Amount conditions and then use sum () .

Create a variable for @Concat

If Unit is 1, then Amount = +Amount

If Unit is -1, then Amount = -Amount

If Unit is 0, then Amount = 0

Place the above formula in the report and then select the Sum() from analysis tab.

Thanks,

Jothi