Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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()))
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
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.
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.
Try like that:
If(
[Ratio] >= Num#('$(vBlockCColorBound)','#,##',','), LightBlue(),
If([Ratio] >= Num#('$(vBlockCColorBoundLow)','#,##',','), Green(),
LightMagenta()))
My suggestion worked - I have tried it and it's different to your formate-code, compare:
'#,##', '.', ','
with
'#,##',',','.'
(decimal- and thousand char is reversed).
- Marcus
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.
Sébastien,
thanks for your reply. I added your proposal to the answer below.
Try formatting both sides of the If
If(
Num([Ratio],'#,##',',') >= Num($(vBlockCColorBound),'#,##',','), LightBlue(),
If(Num([Ratio],'#,##',',')>= Num($(vBlockCColorBoundLow),'#,##',','), Green(),
LightMagenta()))
HTH
Sasi