Discussion board where members can get started with Qlik Sense.
I'm trying to make the variable in this expression work:
If( Avg (If( D_SERVICIO like '6*',I_TOTAL)) > $(media_ano_tm)*1.1, red(), If( Avg (If( D_SERVICIO like '6*',I_TOTAL)) > $(media_ano_tm)/1.1, yellow(), green()))
But for some reason it doesn't work.
I've also tried (unsuccessfully) this:
And If I substitute the variable with a number, the expression seems to work fine.
What could be wrong with it?
Any suggestion or guidance will be highly appreciated.
What's the exact definition of your variable?
Does the variable expands to a number?
And the number is formatted with decimal separator '.' (if any)?
I think that need to put equal sign inside.
Num( Avg (If( D_SERVICIO like '6*',I_TOTAL)),'0,000')
Yes. Please refer to the expression.
Are you using an equal sign in variable definition?
The Magic of Variables
If this is not the case, your final expression will show an aggregation function embedded in another aggregation function (Avg() embedded in Avg() which in general will not work.
See above. It's critical how you've defined the variable.
No. If you use the comma as thousand separator, it will not work in an expression. The thousand separator is only for display purposes.
There has to be an equal to sign before the variable name in the expression.
Thanks, Leandro, but it doesn't work that way either.
Thanks for all the replies. I'm going to continue trying to make your suggestions work.
In the case I get it, I'll let you know.
Double check the format of the number returned by the dollar sign expansion. It will not work if you use comma as thousand separator.
To sum up, some more information about the issue.
My variable is as follows:
Note. The comma refers to decimals, not thousands.
The master measurement has the same definition as the variable above:
What I want to get is to combine this variable with the measurement to set the background colour, as seen in the next image:
If I use the following expresion for "background colour", it doesn't work, as the definitions of both variable and measuremente are the same:
I.e, the shown result will be always green, as If (Avg (If ( D_SERVICIO like '6*', I_TOTAL)) is the same as $(media_ano_tm):
And that's all.