topic Re: Format negative number as red and in brackets in QlikView

Format negative number as red and in brackets

paul_ripley — Thu, 15 Feb 2018 15:54:05 GMT

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

Re: Format negative number as red and in brackets

Peter_Cammaert — Thu, 15 Feb 2018 16:01:34 GMT

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.

Re: Format negative number as red and in brackets

Peter_Cammaert — Thu, 15 Feb 2018 16:05:25 GMT

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.

Re: Format negative number as red and in brackets

Peter_Cammaert — Thu, 15 Feb 2018 16:17:13 GMT

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...

Re: Format negative number as red and in brackets

paul_ripley — Thu, 15 Feb 2018 16:18:34 GMT

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())

Re: Format negative number as red and in brackets

paul_ripley — Thu, 15 Feb 2018 16:23:36 GMT

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