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
-
Good to know!
And thank you for the help
1
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 39 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 260 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 105 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有