Beast Mode Turning numbers into a Letter Grade
Hey everyone,
I am currently working on a project where I have built a data set to see the turn around times on our containers coming in to our warehouses. I am trying to make it so that it will assign each warehouse a letter grade depending on how many days it takes them to empty out a shipping container. I have been trying to write a statement so that it will work but I keep getting a syntax error. Im basically trying to change a range of numbers into letter grades just like they would in high school.
Here are the different statements I have written. None of which have worked thus far. I don't paticularly care which one I use I am just trying to get it to work.
Thanks for your help!
Attempt 1:
(CASE
when `actual_ship_date`-`release_date` >=2 then "A"
when `actual_ship_date`-`release_date`between 2.001 and 3.00 then "B"
when `actual_ship_date`-`release_date`between 3.001 and 4.00 then "C"
When `actual_ship_date`-`release_date`between 4.001 and 5.00 then "D"
when `actual_ship_date`-`release_date`> 5.00 then "F"
else "null")
end
Attempt 2:
if (`actual_ship_date`-`release_date` >=2)
grade = "A";
else if (`actual_ship_date`-`release_date` >= 2.01 && `actual_ship_date`-`release_date` <= 3)
Grade = "B";
else if (`actual_ship_date`-`release_date` >= 3.01 && `actual_ship_date`-`release_date` <= 4)
Grade = "C";
else if (`actual_ship_date`-`release_date` >= 4.01 && `actual_ship_date`-`release_date` <= 5)
Grade = "D";
else if (`actual_ship_date`-`release_date` >= 5.01)
Grade = "F";
End
Attempt 3:
(CASE
when `actual_ship_date`-`release_date`< 2 then "A"
when `actual_ship_date`-`release_date`< 3 then "B"
When `actual_ship_date`-`release_date`< 4 then "C"
When `actual_ship_date`-`release_date`< 5 then "D"
When `actual_ship_date`-`release_date`>= 5 then "F"
Else "Null")
Best Answer
-
It's probably either in your date subtraction or in your assignment of the grade.
I've found it works best to use the DATEDIFF() function instead of simply subracting like you might in certain database queries. BETWEEN doesn't always work well in beast modes, either.
String outputs should also be surrounded by single quotes.
CASE
WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 2 THEN 'A'
WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 3 THEN 'B'
WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 4 THEN 'C'
...
END
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers
-
It's probably either in your date subtraction or in your assignment of the grade.
I've found it works best to use the DATEDIFF() function instead of simply subracting like you might in certain database queries. BETWEEN doesn't always work well in beast modes, either.
String outputs should also be surrounded by single quotes.
CASE
WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 2 THEN 'A'
WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 3 THEN 'B'
WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 4 THEN 'C'
...
END
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
Good to know!
And thank you for the help
1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive