topic Re: Display negative results in QlikView

Display negative results

rdsuperlike — Fri, 08 May 2015 22:41:50 GMT

Want to display -123 as (123) in expressions

Example:

if ((sum(x) + sum(y) +sum(z)) - (sum(x) + sum(y) +sum(k))) < 0

then i want to display (14000) instead of -14000

Any help is appreciated

Re: Display negative results

petter — Fri, 08 May 2015 23:05:05 GMT

=Num(14000,'0;(0)') will return 14000

=Num(-14000,'0;(0)') will return (14000)

Or more complete:

=Num(-14000,'# ##0;(# ##0)' , ',' , ' ' ) will return (14 000)

With decimal separator and thousands separator specified as the third and fourth parameter.

Re: Display negative results

rdsuperlike — Fri, 08 May 2015 23:21:39 GMT

i didnt understand how to incorporate it into my expression.

can u plz help me?

Re: Display negative results

mrossoit — Fri, 08 May 2015 23:28:45 GMT

Encase your entire expression into fabs() function

=fabs((sum(x) + sum(y) +sum(z)) - (sum(x) + sum(y) +sum(k)))

Regards

Re: Display negative results

rdsuperlike — Fri, 08 May 2015 23:35:44 GMT

ok...but it returns positive digit if it is negative. But I want to show (14000) if it is -14000

Re: Display negative results

mrossoit — Fri, 08 May 2015 23:46:26 GMT

So the difference is only brackets?

Try this:

if ((sum(x) + sum(y) +sum(z)) - (sum(x) + sum(y) +sum(k)) < 0, '(' & (sum(x) + sum(y) +sum(z)) - (sum(x) + sum(y) +sum(k)) & ')', (sum(x) + sum(y) +sum(z)) - (sum(x) + sum(y) +sum(k)))

It's ugly but will shows (14000).

Re: Display negative results

petter — Fri, 08 May 2015 23:48:27 GMT

Then you simply do this:

=Num( sum(x) + sum(y) + sum(z) - ( sum(x) + sum(y) + sum(k) ) , '0;(0)' )

Re: Display negative results

MarcoWedel — Sat, 09 May 2015 06:16:49 GMT

Dual('('&fabs( yourexpression)&')', yourexpression)

Re: Display negative results

rdsuperlike — Mon, 11 May 2015 05:48:00 GMT

doesnt work for me.

It still gives me negative value.

Re: Display negative results

PrashantSangle — Mon, 11 May 2015 05:57:28 GMT

Hi,

did you try peter suggestion,

There is no need of If statement,

Try like

Num((sum(x) + sum(y) +sum(z)) - (sum(x) + sum(y) +sum(k))),,'# ##0;(# ##0)')

Regards

Re: Display negative results

rdsuperlike — Mon, 11 May 2015 17:36:24 GMT

It gives same value. It doesnt give brackets accross negative value.

I want (14000) if the value is -14000

Re: Display negative results

petter — Mon, 11 May 2015 18:28:51 GMT

How is your PC set up with National Language settings? US or some other number formatting?

Re: Display negative results

santhoo_san — Mon, 11 May 2015 18:35:49 GMT

Under number tab of the chart properties, select expression default to give effect of the num function explained by the other friends, or select integer / fixed to and update # ##0;(# ##0) in the Format pattern.

Re: Display negative results

rdsuperlike — Mon, 11 May 2015 19:28:16 GMT

Re: Display negative results

petter — Mon, 11 May 2015 19:35:18 GMT

Then it is a mystery to me why you can't get this simple thing to work ...

Re: Display negative results

Anonymous — Mon, 11 May 2015 20:37:18 GMT

It certainly works...

Re: Display negative results

MarcoWedel — Mon, 11 May 2015 22:48:35 GMT

Hi,

another sample application:

using this sample data:

I reduced

(sum(x) + sum(y) +sum(z)) - (sum(x) + sum(y) +sum(k))

sum(z)-sum(k)

as it should be equivalent.

IMO petter-s‌ approach, defining a format string, is the most straight forward and elegant solution to your problem.

hope this helps

regards

Marco

Re: Display negative results

rdsuperlike — Tue, 12 May 2015 00:49:56 GMT

It works for the example that Michael Solomovich showed.

But it didnt work for my expression.

here is what I did

Num( RangeSum(Before((sum(P_QTY) + sum(D_QTY) +sum(ND_QTY))

(sum(P_QTY)+ sum(D_QTY) +

SUM(IP_QTY-WIP_QTY) + IF(GetSelectedCount(ITEM_NAME)>0, SUM(IP_QTY)*MAX(SPLIT_PCNT),SUM(IP_QTY))

+ Sum(O_QTY) + SUM(O_QTY)),0,ColumnNo())) , '0;(0)' )

I think I am messing up bcz of the long expression.

Re: Display negative results

Anonymous — Tue, 12 May 2015 11:21:08 GMT

First, see if the expression works without formatting.

If it doesn't, fix it first. If it does, add formatting:

num(<your long expression>, '#,##0;(#,##0)')