How to get rid of a mirror negative and convert it to number

[Ale P.]

Hello,

I am creating a report to show trendy balances. My source data includesmirror negatives, therefore Spotfire is not reading this values as numbers.

I cannot edit the data source before connecting it to Spotfire,as this includes live data that I would like to automatically load to my report, which ill schedule to refresh automatically afterwards.

Does anybody know how to get rid of the mirror negatives I have tried to change the type from string to real, currency, basically all the options without success.

In excel I use this formula to change the minus sign from the righ to the left:=IF(RIGHT(G6,1)="-",LEFT(G6,LEN(G6)-1)*-1,G6), but i havent managed to make it work in Spotfire, im not even sure if they both use the same formula logic.

Would really appreciate your support!

Thank you!

[Fabian Duerr]

Please try the following:

Real(If(Right([yourCol],1)='-',Concatenate('-',Left([yourCol],-1)),[yourCol]))Replace Real with Integer if needed.

The multiplication with -1 will not work. Because at that point it is still a string. Therefore you should get your minus with the concetenate function. At the end transform to a number.