Variable inside an if statement

[#1]

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.

[swuehl]

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.

[swuehl]

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