topic Add sign (+/-) using formatting in QlikView

Add sign (+/-) using formatting

Mon, 22 Jul 2013 12:31:21 GMT

Hi,

I was wondering if it is possible to add - using formatting options - a leading + or - depending on the value of the number.

I know one can achieve it like this:

=if(num(5)<0,'-','+') & '5'

which should return "+5".

(e.g. 5 should ofcourse be a dynamic field).

Thanks!

Maarten

Re: Add sign (+/-) using formatting

kaushiknsolanki — Mon, 22 Jul 2013 12:33:04 GMT

Hi,

Yes its possible, but more on depends on the requirement.

Regards,

Kaushik Solanki

Re: Add sign (+/-) using formatting

Mon, 22 Jul 2013 12:37:09 GMT

It needs to be done in a text box... holding a complex expression.

Because I don't want to evaluate the result (number; postive or negative) twice (one for getting the sign, and one for displaying the actual value) I wanted to use a formatting option (if available).

Hope this makes things a bit more clear.

Thanks!

Re: Add sign (+/-) using formatting

kaushiknsolanki — Mon, 22 Jul 2013 12:40:22 GMT

Hi,

But when you evaluate the expression, it will show the '-' negative sign if the value is negative and for positive no sign.

Then why you need to calculate sign separately.

Regards,

Kaushik Solanki

Re: Add sign (+/-) using formatting

pennetzdorfer — Mon, 22 Jul 2013 12:43:40 GMT

use =num(5, '+ #.##0,00; - #.##0,00; +/- 0,00')

The order of format-code parts is positive / negative / 0

Regards,

Florian

Re: Add sign (+/-) using formatting

Mon, 22 Jul 2013 12:58:17 GMT

Hi Florian,

That is exactly the result I was looking for.

Evaluating the expression once, and getting a plus or a minus sign.

Thanks!

Maarten

Re: Add sign (+/-) using formatting

Mon, 22 Jul 2013 13:03:32 GMT

Hi guys,

I think Florian was able to supply me what I need.

All solutions where '5' was put more than once in the expression might have been good, but I wanted to avoid evaluating '5' multiple times (as '5' should be replaced by a complex expression in reality).

Thanks to all for your very fast responses!

Kind regards,

Maarten

Re: Add sign (+/-) using formatting

Mon, 22 Jul 2013 13:05:05 GMT

I needed to see the + as well (which is by default not displayed for postive numbers).

Re: Add sign (+/-) using formatting

Mon, 15 Sep 2014 19:45:58 GMT

Just use one simple format - below will add a + for positive numbers, and leave the - for negative numbers:

So use: num(5, '+#,##0.00')