6 Replies Latest reply: Oct 24, 2016 11:24 AM by Sunny Talwar

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

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!

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

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

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

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.

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

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')

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

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'))

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

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)))

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

Yellow, Green, Yellow, Green will be red