topic Colour Coding Using a Data island - Not Able to use AND/OR ?? in QlikView

Colour Coding Using a Data island - Not Able to use AND/OR ??

Thu, 29 Jul 2010 16:03:12 GMT

Hi guys,

I have created a data island so people can change the colour code of a cell based on the value of 2 variables which I have created using two inline tables i.e. Data Island model.

$(vRAGPercentage) gives a numerical value from an aggregate caculation
$(vWarningValueVariable) is a range of numbers as is $(vCriticalValueVariable)

Therefore, in my colour code I want to write:

if( $(vRAGPercentage) < $(vWarningValueVariable) AND
 $(vRAGPercentage) > $(vCriticalValueVariable), rgb(255,128,0),
rgb(105,208,56))

The problem is that it gives the error 'BAD FIELD NAME 'AND' '. In many other colour expressions I have been able to use AND so I am a bit stumped as to why it will now not work.

Any ideas?

Thanks alot

Colour Coding Using a Data island - Not Able to use AND/OR ??

jonathandienst — Thu, 29 Jul 2010 16:34:58 GMT

What is the value of vWarningValueVariable?

You said a "range of numbers", but this expression will only work if it contains a a single number. Perhaps I did not undertsand you correctly.

Jonathan

Colour Coding Using a Data island - Not Able to use AND/OR ??

Thu, 29 Jul 2010 16:54:01 GMT

Hi Jonathan,

I created 2 Inline tables with values 10,20,30,40,50,60,70,80,90 named WarningValue and CriticalValue. I then created a variable for each. The variable for each, (vWarningValueVariable and vCriticalValueVariable) just had were WarningValue and CriticalValuerespectively. I then created a slider object for both using both fields so the user could choose their desired single value for both.

So, for example, if the user chose the WarningValue to be 80 and the CriticalValue to be 70 and the RAGPercentage was 75, the cell should turn Amber - rgb(255,128,0).

The problem is that if I replace my vWarningValueVariable and vCriticalValueVariable with hardcoded numbers it lets me use the AND function, but when I switch this to a variable it doesn't seem to work.

This make it any clearer?

Colour Coding Using a Data island - Not Able to use AND/OR ??

jonathandienst — Thu, 29 Jul 2010 19:03:42 GMT

Have you tried using the variables directly, rather than via a $ expansion:

if(vRAGPercentage < vWarningValueVariable AND vRAGPercentage > vCriticalValueVariable, rgb(255,128,0), rgb(105,208,56))

Jonathan