Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jblomqvist
Specialist
Specialist

How to write a massive IF statement like this to produce a colour based on two measures?

Hi all,

Long explanation below but the idea is simple!

I have the following table which defines what a colour should be when comparing output from two Measures:

Measure 1: % Variance

Measure 2: % Projects

For each Measure I already have an individual expression that tells me if the value is in Red, Green or Yellow. These are held in two variables:

Variable1: gives me one specific colour for %Variance

Variable2: gives me one specific colour for % Project

I need a large IF statement (I think unless there is a better alternative) that produces each value in the Output column below based on the colour combination of % Variance and % Projects output.

% Variance

% Projects

Output

Red

Red

Red

Red

Yellow

Red

Red

Green

Red

Yellow

Yellow

Yellow

Yellow

Red

Red

Yellow

Green

Yellow

Green

Green

Green

Green

Red

Red

Green

Yellow

Yellow

The basic idea if you were to read it is:

If % Variance is Red and % Projects is Red, then colour is Red,

If % Variance is Red and % Projects is Yellow, then colour is Red

If % Variance is Red and % Projects is Green, then colour is Red

If % Variance is Yellowand % Projects is Yellow, then colour is Yellow

... and so on following the rules of table above.

I tried:

If($(v%Variance)='Red' and $(v%Projects)='Red', 'Red',

If($(v%Variance)='Red' and $(v%Projects)='Yellow', 'Red',

If($(v%Variance)='Red' and $(v%Projects)='Green', 'Red',

If($(v%Variance)='Yellow' and $(v%Projects)='Yellow', 'Yellow',

If($(v%Variance)='Yellow' and $(v%Projects)='Red', 'Red',

If($(v%Variance)='Green' and $(v%Projects)='Green', 'Green',

If($(v%Variance)='Green' and $(v%Projects)='Red','Red',

If($(v%Variance)='Green' and $(v%Projects)='Yellow','Yellow'

)

)

)

)

)

)

But this doesn't work!

Can anyone please provide an IF statement for the above table to produce the output in the Output column?

6 Replies
sunny_talwar

May be use Pick Match instead of if

Pick(Match($(v%Variance)&'|'&$(v%Projects), 'Red|Red', ......),

'Red',

.

.

.

)

sunny_talwar

Or even better option would be to assign a dual value to your variable

Red = 3

Yellow = 2

Green = 1


and then Pick(RangeMax(var1, var2), 'Green', 'Yellow', 'Red')

jblomqvist
Specialist
Specialist
Author

Hi Sunny,

Would you be able to provide a practical working example I can copy?

I tend to get lost when I see so many brackets and commas! Copying a sample and changing it may help with better understanding

sunny_talwar

Try this:

If(WildMatch('|'&$(v%Variance)&'|'&$(v%Projects)&'|', '*Red*'), 'Red',

     If(WildMatch('|'&$(v%Variance)&'|'&$(v%Projects)&'|', '*Yellow*'), 'Yellow', 'Green'))

jblomqvist
Specialist
Specialist
Author

Hi Sunny,

Is this the complete code for the table above or do I need to add? Sorry I am unable to work it out yet

sunny_talwar

I should work, isn't it working?