11 Replies Latest reply: Apr 19, 2016 6:19 AM by Stefan Wühl

# 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.

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

• ###### 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.

• ###### 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')

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.

• ###### Re: Variable inside an if statement

\$(=media_ano_tm)

• ###### Re: Variable inside an if statement

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

• ###### Re: Variable inside an if statement

Hi Manuel,

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

• ###### 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.

• ###### 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.

• ###### Re: Variable inside an if statement

Hi there.

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.

Thanks.

• ###### Re: Variable inside an if statement

I might not made my point clear:

If you use a dollar sign expansion to replace your variable with a number, the number needs to be formatted like dot used for decimal separator, no thousand separator. If you are using a comma, the comma will indicate Start of the THEN branch in your if() statement.

You could try enclosing the variable in single quotes, so QS needs to interprete your string as number, or use \$(#vVar) expansion (though I think this has not been working reliable in the past), or format your number correctly that it will match the internal number format.

Not sure if this made it clear, yet hoping.

• ###### Re: Variable inside an if statement

And as a second point, please also double check that you've created your variable with a leading equal sign, so the dollar sign expansion will return a number, not the avg() function itself (then you would run into embedding an aggregation into another aggregation, something that in general does not work without using advanced aggregation (or TOTAL qualifier).