Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
May be use Pick Match instead of if
Pick(Match($(v%Variance)&'|'&$(v%Projects), 'Red|Red', ......),
'Red',
.
.
.
)
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')
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
Try this:
If(WildMatch('|'&$(v%Variance)&'|'&$(v%Projects)&'|', '*Red*'), 'Red',
If(WildMatch('|'&$(v%Variance)&'|'&$(v%Projects)&'|', '*Yellow*'), 'Yellow', 'Green'))
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
I should work, isn't it working?