topic Re: IF statement as a Variable for Background Colour in QlikView

IF statement as a Variable for Background Colour

Wed, 07 Jun 2017 11:21:05 GMT

I have the following If which is setting the background colour in my straight table.

IF(FORECAST_STATUS='CONFIRMED FDD',rgb(200,240,200),
IF(FORECAST_STATUS='UNCONFIRMED FDD',rgb(255,153,0),
IF(FORECAST_STATUS='NO FDD',rgb(255,80,80),'')))';

On its own it works fine but so that I don’t have to amend it 2 dozen times I assumed I could set it as a Variable and apply the variable to the background colour instead.

The set variable is scripted as follows in the first tab:

SET vFDDSTATUS = '=IF(FORECAST_STATUS='CONFIRMED FDD',rgb(200,240,200),
IF(FORECAST_STATUS='UNCONFIRMED FDD',rgb(255,153,0),
IF(FORECAST_STATUS='NO FDD',rgb(255,80,80),'')))';

My main concern is that FORECAST_STATUS doesn’t exist this early………………or is that not a concern when setting variable?

Thank You

Re: IF statement as a Variable for Background Colour

sunny_talwar — Wed, 07 Jun 2017 11:26:03 GMT

It should not be a problem if FORECAST_STATUS doesn't exists. What might be a concern is that fact that you have single quote within the single quote here. Not sure how it will behave, but give it a shot and see what happens?

Re: IF statement as a Variable for Background Colour

Wed, 07 Jun 2017 11:30:06 GMT

Afternoon,

oh it definitely doesn't work but I am not getting a failure and the variable is being created.

Like I say, using the statement on it own in the Background Colour setting works fine.

Re: IF statement as a Variable for Background Colour

sunny_talwar — Wed, 07 Jun 2017 11:31:33 GMT

Do you see the variable in the variable overview? How does it look within the variable overview?

Re: IF statement as a Variable for Background Colour

Wed, 07 Jun 2017 11:35:50 GMT

admittedly in the overviewer the first and last '' exist so it may be that..............

Re: IF statement as a Variable for Background Colour

sunny_talwar — Wed, 07 Jun 2017 11:37:22 GMT

Then try it like this and see what happens

SET vFDDSTATUS = =IF(FORECAST_STATUS='CONFIRMED FDD',rgb(200,240,200),
IF(FORECAST_STATUS='UNCONFIRMED FDD',rgb(255,153,0),
IF(FORECAST_STATUS='NO FDD',rgb(255,80,80),'')));

SET vFDDSTATUS = IF(FORECAST_STATUS='CONFIRMED FDD',rgb(200,240,200),
IF(FORECAST_STATUS='UNCONFIRMED FDD',rgb(255,153,0),
IF(FORECAST_STATUS='NO FDD',rgb(255,80,80),'')));

Notice that the second expression doesn't have an equal sign

Re: IF statement as a Variable for Background Colour

Wed, 07 Jun 2017 12:02:23 GMT

worked it out:

SET vFDDSTATUS = IF(FORECAST_STATUS='CONFIRMED FDD',rgb(200,240,200),
IF(FORECAST_STATUS='UNCONFIRMED FDD',rgb(255,153,0),
IF(FORECAST_STATUS='NO FDD',rgb(255,80,80),'')));

then use variable as such:

=$(vFDDSTATUS)