topic Re: Problem with format-code in Num() in expression in QlikView

Problem with format-code in Num() in expression

andreas_koehler — Sun, 14 Feb 2016 01:34:51 GMT

Hi!

This is about colorcoding a simple table with If-clauses in the expression field of the background color and using variables to adapt the limits.

It did not work out and I wonder why?

As this works:

If( [Ratio] >= 0.1, LightBlue(),

If([Ratio] >= -0.1, Green(),

LightMagenta()))

this does not:

If(

[Ratio] >= Num($(vBlockCColorBound),'#,##',','), LightBlue(),

If([Ratio] >= Num($(vBlockCColorBoundLow),'#,##',','), Green(),

LightMagenta()))

with

vBlockCColorBound = 0,1

vBlockCColorBoundLow = -0,1

Seems that I always get a 0 as result of both Num().

[Ratio] is the label of the expression I want to colorcode.

Both valuables are in the end defined by users via sliders with German number conventions (DecimalSep=',') that should not be changed.

Re: Problem with format-code in Num() in expression

sunny_talwar — Sun, 14 Feb 2016 03:14:59 GMT

what do you get when you $(vBlockCColorBound). Not sure if you need Num() function if you have its value as 0.1. May be this can work:

If([Ratio] >= $(vBlockCColorBound), LightBlue(),

If([Ratio] >= $(vBlockCColorBoundLow), Green(), LightMagenta()))

or try it with a equal sign within dollar sign expansion

If([Ratio] >= $(=vBlockCColorBound), LightBlue(),

If([Ratio] >= $(=vBlockCColorBoundLow), Green(), LightMagenta()))

Re: Problem with format-code in Num() in expression

marcus_sommer — Sun, 14 Feb 2016 09:00:20 GMT

Like the suggestion from Sunny try it without Num() and then with Num() without specifying a format. If not respectively every time if you need to specify the comma-sign and/or the thousand-delimiter you need to apply Num() with all 3 parameter, like:

= num(vTest, '#,##', '.', ',') // vTest: 0,1

- Marcus

Re: Problem with format-code in Num() in expression

andreas_koehler — Sun, 14 Feb 2016 21:02:22 GMT

Sunny,

thanks for your reply.

Actually I started with this expression:

If([Ratio] >= $(vBlockCColorBound), LightBlue(),

If([Ratio] >= $(vBlockCColorBoundLow), Green(), LightMagenta()))

and what happened was that no background color was set at all. (it remained white).

Same happens with $(=vBlockCColorBound).

Please bear in mind that the value is 0,1 or similar and not 0.1 as I need to use the German separator conventions.

Re: Problem with format-code in Num() in expression

andreas_koehler — Sun, 14 Feb 2016 21:30:20 GMT

Marcus,

Without Num() or with Num() without specifying the format I get the same result as when I would write

If( [Ratio] >= 0,1, LightBlue(),

If([Ratio] >= -0,1, Green(),

LightMagenta()))

In all 3 cases I get no valuespecific change in the background color. It is just white.

My understanding was that I need to interprete the 0,1 as 0.1 and that this is what I do with the second parameter of Num(). Sadly it does not work.

What also leave the background color set to white is:

If(

[Ratio] >= Num($(vBlockCColorBound),'#,##',',','.'),

LightBlue(),

If(

[Ratio] >= Num($(vBlockCColorBoundLow),'#,##',',','.'),

Green(),

LightMagenta()))

What does work but does not solve the issue if I set both values using english decimal conventions. So all works well if

vBlockCColorBound = 0.1

vBlockCColorBoundLow = -0.1

but this does not resolve the issue. It just proves that that there is no syntax error in the expressions etc.

Re: Problem with format-code in Num() in expression

sfatoux72 — Sun, 14 Feb 2016 23:06:13 GMT

‌Try like that:

If(

[Ratio] >= Num#('$(vBlockCColorBound)','#,##',','), LightBlue(),

If([Ratio] >= Num#('$(vBlockCColorBoundLow)','#,##',','), Green(),

LightMagenta()))

Re: Problem with format-code in Num() in expression

marcus_sommer — Mon, 15 Feb 2016 06:22:43 GMT

My suggestion worked - I have tried it and it's different to your formate-code, compare:

'#,##', '.', ','

with

'#,##',',','.'

(decimal- and thousand char is reversed).

- Marcus

Re: Problem with format-code in Num() in expression

andreas_koehler — Mon, 15 Feb 2016 10:04:28 GMT

Marcus,

thanks for the reply.

Recap: I need to translate the input of 0,1 (via input box or later as slider with %) into an output value that can be read by the expression in the background color.

My settings are and should be

SET ThousandSep='.';

SET DecimalSep=',';

I tried out various stuff as xxx in the following code: If( [Ratio] >= xxx, LightBlue() with $(vBlockCColorBound).

interpreted as: A textfile with = xxx

Result: the colorcode in the straight table. The value of the second variable vBlockCColorBoundLow is the negative value of vBlockCColorBound

xxx interpreted as Result

0.1 0.1 proper result

0,1 0,1 all white

$(vBlockCColorBound)= 0.1 0.1 proper result

$(vBlockCColorBound)= 0,1 error all white

Num($(vBlockCColorBound)) with 0,1 10 positive values are blue, negative magenta;

Num($(vBlockCColorBound),'#,##',',','.') error(Num takes 1-4 parameters)

all white

Num($(vBlockCColorBound),'#.##',',','.') error(Num takes 1-4 parameters)

all white

Num#('$(vBlockCColorBound)','#.##',',','.') 0,1 positive values are blue, negative magenta;

Num#('$(vBlockCColorBound)','#,##',',','.') 0,1 positive values are blue, negative magenta;

I reread the definition in the ref-file:

num(expression [ , format-code [ , decimal-sep [ , thousands-sep ]] ] )

The num function formats the expression numerically according to the string given as format-code. Decimal

separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted,

the number format set in the operating system is used.

I read this as such that with a format-code '#.##' with a dot as separator I would interprete a 0,1 (with a comma) as a 0.1 (with a dot). Adding the optional decimal separator as ',' I make it clearer to Qlikview that a comma should be interpreted as decimal separator.

What is wrong here?

I am surprise that something that is very simple with english sepator conventions turns out to be such a beast when you have to use a comma as decimal separator.

Re: Problem with format-code in Num() in expression

andreas_koehler — Mon, 15 Feb 2016 10:10:54 GMT

Sébastien,

thanks for your reply. I added your proposal to the answer below.

Re: Problem with format-code in Num() in expression

sasiparupudi1 — Mon, 15 Feb 2016 12:37:21 GMT

Try formatting both sides of the If

If(

Num([Ratio],'#,##',',') >= Num($(vBlockCColorBound),'#,##',','), LightBlue(),

If(Num([Ratio],'#,##',',')>= Num($(vBlockCColorBoundLow),'#,##',','), Green(),

LightMagenta()))

HTH

Sasi

Re: Problem with format-code in Num() in expression

marcus_sommer — Mon, 15 Feb 2016 16:30:12 GMT

At first if you want to change the decimal-sign you need to apply the num-function with all 3 format-parameter. But the decimal-sign which you applied within the first parameter '#,##' isn't relevant because it will be changed with the second parameter which must be then '.'. If you used my suggestion from above:

= num(vTest, '#,##', '.', ',') // vTest: 0,1

it will work.

- Marcus

Re: Problem with format-code in Num() in expression

andreas_koehler — Mon, 15 Feb 2016 19:57:21 GMT

Marcus,

you are right, it works:

If( [Ratio] >= Num(vBlockCColorBound,'#,##','.',',') , LightBlue() ...

What also worked was Sébastien's proposal with a slight change:

If(

[Ratio] >= Num#('$(vBlockCColorBound)','#,##','.',',') ,

I also tested Sadi's proposal, but is sadly did not work well, even with some variations.

If(

Num([Ratio],'#,##',',') >= Num($(vBlockCColorBound),'#,##',',') ,

but is sadly did not work well, even with some variations.

Thank you all!