Change number formatting if specific value is used

[fgirardin]

Hello,

Is there a way to format a number string if a specific value in a table = xxx ?

I've got some sales data formatted as such

Document N°     Operation                    Amount

0001                  DBBL                         150

0002                  DBBL                         200

0003                  DBNC                          50

All my numbers in amount are positive, even when we credit customer (wich should become a negative value in my chart so I can get the sum of all operations, positive - negative)

In my example, if I ask to get the sum of "Amount", the result will be:   400

But document 0003 (operation DBNC) should be a negative value. so the correct result should be: 350 (150+200+(-50))

note: My number format for money is: #'###.00    (  ' as thousand separator /  . as decimal separator  )

I tried the following expression with no success:

=if(Operation='DBNC', num(Amount, -#''###.00), num(Amount, #''###.00)) 

Obviously, my expression is not correct as I don't see any change in formatting

Any help on how to correct this ?

Thank you

FG

[swuehl · Accepted solution]

Try something like

=Sum( If( Operation = 'DBNC', -1*Amount, Amount))

Only changing the format won't return the correct aggregated values.

If you need to adjust also the format codes, you can specify the format for positive and negative values separated by semicolon:

Num( Value, '#''###.00;-#''###.00','.','''')

[I tried to escape the single quotes by using two single quotes]

[fgirardin]

Thank you Stefan, it works perfectly with your expression !

I wish you all the best

FG