Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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(

{<

[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

1 Solution

Accepted Solutions
Not applicable

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.

4 Replies
Not applicable

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.

Not applicable

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

Not applicable

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.

Not applicable

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

Bingo! Thanks!!!

Community Browser