Best approach via Beast Mode - multiple if/case statements
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
-
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! ✔️**0
Answers
-
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! ✔️**0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 695 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive