Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

jblomqvist
Contributor II

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

vOrdersvDiscounts

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!

6 Replies

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?

jblomqvist
Contributor II

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.

varshavig12
Valued Contributor

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

maico_sandri1
New Contributor II

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

stabben23
Honored Contributor

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

Yellow, Green, Yellow, Green will be red

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

Community Browser