4 Replies Latest reply: Apr 11, 2013 7:44 AM by Dror Svartzman

# BUG(?): Dollar-Sign Expansion with Parameters

Hi,

I use the following inline function to determine background color of textbox:

```/*
Calculate the background color based on the Actual, Budget and Actual%
(1) - Actuals
(2) - Budget
(3) - %Budget

*/
SET BackgroundColor = IF(\$2 = 0, // IF Budget = 0
IF(\$1 = 0, //...and also actuals = 0
RGB(221,221,221), //  = Grey,
RGB(128,255,128) // = GREEN, actuals <> 0
),
IF(\$3 >=1,
RGB(128,255,128), //GREEN
IF(\$3 >=0.75,
RGB(255,255,128), //YELLOW
RGB(255,66,66) //red
)
)
)

;

The actual implementation looks something like this:
=\$(BackgroundColor(\$(=only({<[KPI nr] = {'1'}>} Actual)), \$(=Only({<[KPI nr] = {'1'}>} Budget)),\$(=Only({<[KPI nr] = {'1'}>} %Budget))))

I use another dollar-expansion variable to retrieve the relavant KPI's expression an evaluate on the fly

First parameter (Actual)
sum(
{<
>}[#Transact bedrag])

Second parameter (Budget)
sum( {<
[Verdichting1]= {'Total Revenue from Services','Total Costs of Services'}
>}#Budget)

Third parameter (%Budget)
\$(=only({<KPI = {'Gross Profit'}>}[Actual])) / (\$(=only({<KPI = {'Gross Profit'}>}[Budget])))

```

Here's the problem. the inline function (BackgroundColor) will fail if one of the parameters contains a comma (in the above example - second parameter). Otherwise it works just fine.

Any idea's?

Dror

• ###### Re: BUG(?): Dollar-Sign Expansion with Parameters

Commas in the parameter list always separate the parameters; as far as I know there is no way to escape it (pun intended). The only way around it is to not pass a comma. In your example, try passing the [KPI nr] value and the field names, Actual, Bugdet, and %Budget, and resolve the expression in the BackgroundColor variable.

• ###### Re: BUG(?): Dollar-Sign Expansion with Parameters

oh. I thought setting the expression in dollar expansion will cause Qlikview to first evaluate it to value and then pass it to the function. I'll keep on looking...

• ###### Re: BUG(?): Dollar-Sign Expansion with Parameters

Your example has dollar sign expansion with evaluate but I suspect you need two levels. Try the following:

=\$(BackgroundColor(\$(=\$(=only({<[KPI nr] = {'1'}>} Actual))), \$(=\$(=Only({<[KPI nr] = {'1'}>} Budget))),\$(=\$(=Only({<[KPI nr] = {'1'}>} %Budget)))))

Again, you need to make sure the results of the three expressions do not have any commas.

• ###### Re: BUG(?): Dollar-Sign Expansion with Parameters

Bingo! Thanks!!!