Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

andreas_koehler
Contributor II

Problem with format-code in Num() in expression

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.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

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

View solution in original post

11 Replies

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

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

MVP & Luminary
MVP & Luminary

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

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

View solution in original post

andreas_koehler
Contributor II

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

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
Contributor II

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

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.

Partner
Partner

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

Try like that:

If(

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

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

LightMagenta()))

MVP & Luminary
MVP & Luminary

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

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
Contributor II

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

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
Contributor II

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

Sébastien,

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

sasiparupudi1
Honored Contributor III

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

Try formatting both sides of the If

If(

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

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

LightMagenta()))

HTH

Sasi