Best approach via Beast Mode - multiple if/case statements

Options

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!

Best Answer

  • david_cunningham
    Answer ✓
    Options

    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

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • david_cunningham
    Answer ✓
    Options

    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

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**