6 Replies Latest reply: Oct 14, 2016 9:51 AM by Sunny Talwar RSS

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

    John Blomqvist

      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?