# Best approach via Beast Mode - multiple if/case statements

Member

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!

• Coach

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

** Did this solve your problem? Accept it as a solution! ✔️**

• Coach

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