BUG(?): Dollar-Sign Expansion with Parameters

[#1]

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( 
{<
 [Exec P&L Heading]= {'Gross Profit'}
>}[#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?

Many thanks in advance,

Dror

[#2]

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.

[#3]

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

[#4 · Accepted solution]

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.

[#5]

Bingo! Thanks!!!