Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
paul_ripley
Contributor III

Format negative number as red and in brackets

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

1 Solution

Accepted Solutions

Re: Format negative number as red and in brackets

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

5 Replies

Re: Format negative number as red and in brackets

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

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

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
Contributor III

Re: Format negative number as red and in brackets

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
Contributor III

Re: Format negative number as red and in brackets

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