Format negative number as red and in brackets

[paul_ripley]

Hello

I have an expression

=NUM((floor(sum({$<period = {$(vEndPeriod_CY)}, pl = {F01}>} IS_Investment_GBP) +500000,1000000)/1000000 *-1 ) ,'#,##0;(#,##0)')

that I would like to format negative number as red and in brackets and positive as green and no brackets.

I can get it to show the brackets as above but I don't know how to show red or green.

Could anyone help?

Thanks

Paul

[Peter_Cammaert]

On the left of the Expressions tab, expand the tree of this particular expression. The subtree has a leaf called "Text Color". Enter something like this to get a correct color:

IF (expression < 0.0, red(), green())

where you replace expression with the first parameter of your Num() call in your original post. You don't need to repeat the formatting itself, because that doesn't matter for selecitng a color.

[Peter_Cammaert]

Note that you can also enter the format string of your expressions Num() call in the Format Pattern field for this expression on the Properties->Number tab. After that, you can just omit the formatting in the expression itself. Keeps expressions less complex.

[Peter_Cammaert · Accepted solution]

I am assuming that your expression occurs in a Chart/Table. If you put this in a simple text box, then indeed there is no expression tab or an expression tree.

In that case you can use the same technique as I explained before to set the text Font color in the Font tab. Click the Font->Color button, select Base Color = Calculated and enter the expression in the expression field.

One minor disadvantage: all other text in your text box changes color too...

[paul_ripley]

Hi Peter

Thanks for your help

I am actually presenting the numbers in NPrinting Powerpoint

The below gave me in QV

=If(NUM((floor(sum({$<period = {$(vEndPeriod_CY)}, pl = {F01}>} IS_Investment_GBP) +500000,1000000)/1000000 *-1 ) ,'#,##0;(#,##0)')

< 0.0, red(), green())

[paul_ripley]

Ah ok.  I can see what you are doing .  Thanks for your help.  I'll have to think how to incorporate this into my report