topic Re: Strange num() Format Code behavior in QlikView

Strange num() Format Code behavior

pennetzdorfer — Mon, 14 Apr 2014 09:44:01 GMT

Hello,

I've noticed a strange issue when using format codes in num() function:

In my year-to-year comparison I would like to display "+" in front of positive values and "+/-0" for zero values ... that works well but for some reason all negative values get a "+-"

Does anyone have an idea why? Please find the attached qvw.

Thanks,

Florian

Re: Strange num() Format Code behavior

alexandros17 — Mon, 14 Apr 2014 09:55:14 GMT

zero is considered positive so

=num(0, '+#.##0; -#.##0') is enough, if you need that format for zero use a condition:

if(myField = 0,

num(0, '+#.##0; -#.##0; +/-0'),

num(0, '+#.##0; -#.##0')

)

Hope it helps

Re: Strange num() Format Code behavior

tresB — Mon, 14 Apr 2014 10:04:58 GMT

I guess format like: '+#.##0' alone works fine unless you need zero with '+/-' prefix.

Re: Strange num() Format Code behavior

nizamsha — Mon, 14 Apr 2014 10:18:59 GMT

=num(10, '+#.##0; -#.##0')

=num(-10, '+#.##0; -#.##0')

=num(0, '+#.##0; -#.##0')

Re: Strange num() Format Code behavior

pennetzdorfer — Mon, 14 Apr 2014 10:53:43 GMT

Thanks for your suggestions.

@ Allesandro: using an if-condition would be a workaround but I guess it should be possible using simple format codes

@ Tresesco: exactly, that's the problem (bug?!) Using that format code in Excel works as intended.

Unfortunately I can't find anything in the Qlikview Manual ...

Any other ideas?

Regards,

Florian

Re: Strange num() Format Code behavior

sunilkumarqv — Mon, 14 Apr 2014 14:19:55 GMT

use the

Formate Code to identify : '+(#.##0); -(#.##0); +/-0'

these make sense that Positive or Negative values Easily

Re: Strange num() Format Code behavior

pennetzdorfer — Mon, 14 Apr 2014 14:27:54 GMT

Thanks Sunil, but your code is pretty the same - it doesn't work either...

Result for negative value: -(+10)

Re: Strange num() Format Code behavior

rwunderlich — Tue, 20 May 2014 15:32:41 GMT

It strikes me as a bug. If there is any "+" in the third pattern, the "+" shows up in in the negative value.

-Rob

Re: Strange num() Format Code behavior

rbecher — Tue, 12 Aug 2014 20:40:12 GMT

I proposed some workarounds at your post in the QlikBug

Strange num() Format Code behavior

- Ralf