Best approach via Beast Mode - multiple if/case statements

NFerna

Hi everyone,

I have the below data example:

Name	Variety	Benchmark Value	Achieved Value
Apple	Red Apple Batch 23	3	4
Apple	Red Apple Batch 22	N/A	4
Orange	Batch 11 v1	3	4
Orange	Batch 11 v2	5	3
Pear	Batch 20 TVS	2	6
Pear	Batch 24 LKT	4	1
Plum	Batch 25 MBH	N/A	7
Lemon	Batch 2	N/A	5

Ideally I would like to use colors on a table when (in this example) :

• When achieved value is greater or equal to benchmark value, then Green
• When achieved value is less than benchmark value, then Red
• When N/A, stay blank or show N/A

Currently, I am using beastmode and an additional column with a "ABOVE/BELOW" set up as follows:

• CASE

• WHEN Fruit= 'Apple' and Achieved Value >= 3 then 'ABOVE'

• WHEN Fruit= 'Orange' and Achieved Value >= 5 then 'ABOVE'

• WHEN Fruit= 'Pear' and Achieved Value >= 2 THEN 'ABOVE'

• ELSE 'BELOW'

END

This works great for the above/below, however I'm unsure how to incorporate logic so that an "N/A" or "Unavailable" value is passed in the beast mode and displayed in the same column - is there a method here that will allow for the 3 outcomes of "ABOVE", "BELOW", "N/A"?

Thanks!

david_cunningham

You could do

CASE WHEN fruit in ('Apple','Orange','Pear') THEN

CASE

• WHEN Fruit= 'Apple' and Achieved Value >= 3 then 'ABOVE'
• WHEN Fruit= 'Orange' and Achieved Value >= 5 then 'ABOVE'
• WHEN Fruit= 'Pear' and Achieved Value >= 2 THEN 'ABOVE'
• ELSE 'BELOW'

END

ELSE 'N/A'

END

david_cunningham

You could do

CASE WHEN fruit in ('Apple','Orange','Pear') THEN

CASE

• WHEN Fruit= 'Apple' and Achieved Value >= 3 then 'ABOVE'
• WHEN Fruit= 'Orange' and Achieved Value >= 5 then 'ABOVE'
• WHEN Fruit= 'Pear' and Achieved Value >= 2 THEN 'ABOVE'
• ELSE 'BELOW'

END

ELSE 'N/A'

END