Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Variable inside an if statement

Hi there.

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:

...{'$(media_ano_tm)'}...


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.

Thanks.

11 Replies
swuehl
MVP
MVP

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

Not applicable
Author

I think that need to put equal sign inside.

$(=media_ano_tm)

Not applicable
Author

Hi, swuehl.

What's the exact definition of your variable?

Num( Avg (If( D_SERVICIO like '6*',I_TOTAL)),'0,000')

Does the variable expands to a number?

Yes. Please refer to the expression.

And the number is formatted with decimal separator '.' (if any)?

Yes. Please refer to the expression.

Thanks.

swuehl
MVP
MVP

What's the exact definition of your variable?

Num( Avg (If( D_SERVICIO like '6*',I_TOTAL)),'0,000')

Are you using an equal sign in variable definition?

See:

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.

Does the variable expands to a number?

Yes. Please refer to the expression.


See above. It's critical how you've defined the variable.

And the number is formatted with decimal separator '.' (if any)?

Yes. Please refer to the expression.

No. If you use the comma as thousand separator, it will not work in an expression. The thousand separator is only for display purposes.

reddy-s
Master II
Master II

Hi Manuel,

There has to be an equal to sign before the variable name in the expression.

Not applicable
Author

Thanks, Leandro, but it doesn't work that way either.

Not applicable
Author

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.

swuehl
MVP
MVP

Double check the format of the number returned by the dollar sign expansion. It will not work if you use comma as thousand separator.

Not applicable
Author

Hi there.

To sum up, some more information about the issue.

My variable is as follows:

im_1.png

     Note. The comma refers to decimals, not thousands.

The master measurement has the same definition as the variable above:

im_2.png

What I want to get is to combine this variable with the measurement to set the background colour, as seen in the next image:

im_3.png

If I use the following expresion for "background colour", it doesn't work, as the definitions of both variable and measuremente are the same:

im_5.png

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

im_6.png

And that's all.

Thanks.