Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
MVP
MVP

Re: Variable inside an if statement

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

Re: Variable inside an if statement

I think that need to put equal sign inside.

$(=media_ano_tm)

Not applicable

Re: Variable inside an if statement

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.

MVP
MVP

Re: Variable inside an if statement

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.

reddys310
Honored Contributor II

Re: Variable inside an if statement

Hi Manuel,

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

Not applicable

Re: Variable inside an if statement

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

Not applicable

Re: Variable inside an if statement

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.

MVP
MVP

Re: Variable inside an if statement

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

Re: Variable inside an if statement

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.

Community Browser