Problem with format-code in Num() in expression

[andreas_koehler]

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.

[sunny_talwar]

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()))

[marcus_sommer · Accepted solution]

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

[andreas_koehler]

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.

[andreas_koehler]

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.

[sfatoux72]

‌Try like that:

If(

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

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

LightMagenta()))

[marcus_sommer]

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

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

with

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

(decimal- and thousand char is reversed).

- Marcus

[andreas_koehler]

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.

[andreas_koehler]

Sébastien,

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

[sasiparupudi1]

Try formatting both sides of the If

If(

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

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

LightMagenta()))

HTH

Sasi