How to assign a single colour based on four conditions like this? Need an expression

[jblomqvist]

Hi all,

I have the following table.

I need the value in the Outcome column when four different measures each have a certain value.

My variables:

vSales

vMargin

vOrders

vDiscounts

are all variables which hold individiual value (e.g. if Sales is Red, vOrders is Green etc).

So based on the value of the individual variables I need the overall colour to to be what is described in the Outcome column.

Example:

If vSales is Yellow and vMargin is Yellow, and vOrders is Yellow, and vDiscounts is Red, then the Outcome is Red.

and continue as per below spec....

vSales	vMargin	vOrders	vDiscounts	Outcome
Red	Red	Red	Red	Red
Red	Red	Red	Yellow	Red
Red	Red	Red	Green	Red
Yellow	Yellow	Yellow	Yellow	Yellow
Yellow	Yellow	Yellow	Red	Red
Yellow	Yellow	Yellow	Green	Yellow
Green	Green	Green	Green	Green
Green	Green	Green	Red	Red
Green	Green	Green	Yellow	Yellow
Red	Red	Yellow	Yellow	Red
Red	Red	Green	Green	Red
Red	Yellow	Yellow	Green	Red
Red	Green	Green	Yellow	Red
Yellow	Yellow	Red	Green	Red
Yellow	Yellow	Green	Red	Red
Yellow	Red	Green	Yellow	Red
Yellow	Green	Yellow	Red	Red
Green	Green	Red	Yellow	Red
Green	Yellow	Red	Green	Red
Green	Green	Yellow	Red	Red
Green	Red	Green	Yellow	Red

Does anyone know an expression that can create this please?

It seems super complex so not sure how to do it!

[sunny_talwar]

How is the value assigned to the individual variable? Can you share the expressions behind those variables?

[jblomqvist]

Hi Sunny,

It would be along the lines of:

If(Sum(LineSalesAmount)>0.10, 'Green', If(Sum(LineSalesAmount)>0.65, 'Yellow'))

And the other variables have minor variations of the same example above.

[varshavig12]

May be like this :

=if(vDiscounts = 'Red' or vSales ='Red' or vMargin = 'Red' or vOrders = 'Red', 'Red' ,

if(vDiscounts = 'Yellow' or vSales ='Yellow' or vMargin = 'Yellow' or vOrders = 'Yellow', 'Yellow' ,

'Green'))

[Anonymous]

John,

     The best logic you may have find to be the one below.

if(vSales = Green and vMargin = Green and vOrders = Green AND vDiscounts = Green,

      Green,

if(vSales = Green and vMargin = Green and vOrders = Green AND vDiscounts = Yellow,

      Yellow,

if(vSales = Yellow and vMargin = Yellow and vOrders = Yellow AND vDiscounts <> Red,

      Yellow,

      Red)))

[stabben23]

Yellow, Green, Yellow, Green will be red

[sunny_talwar]

Instead of assigning them color, assign them values of 1, 2, 3, 4

Pick(

RangeMax(

If(Sum(LineSalesAmount)>0.10, 3, If(Sum(LineSalesAmount)>0.65, 2, 1)),

.....

),

'Red', 'Yellow', 'Green')