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

284 Views

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.