Date Range Buckets

Hello,
I am trying to create a way to look at upcoming estimated shipping dates but I keep getting an "error during processing" .
I have made buckets for WIP based off days in shop field and basic case formulas in the past with no issues (See attached).
I think I am having issues with my approach due to this being dates instead of a raw value.
In theory:
case when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 3 days) then ‘next 3 days’ else
case when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 7 days) then ‘next 7 days’ else
case when `est_ship_date’ < DATE_ADD(CURRENT_DATE()interval 14 days) then ‘next 14 days’ else
case when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 21 days) then ‘next 21 days’ else
'Over 21 days' end end end end
Best Answer
-
your syntax for a case statement is wrong.
case
when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 3 days) then ‘next 3 days’
when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 7 days) then ‘next 7 days’
when `est_ship_date’ < DATE_ADD(CURRENT_DATE()interval 14 days) then ‘next 14 days’
when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 21 days) then ‘next 21 days’
else 'Over 21 days'
end
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Answers
-
your syntax for a case statement is wrong.
case
when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 3 days) then ‘next 3 days’
when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 7 days) then ‘next 7 days’
when `est_ship_date’ < DATE_ADD(CURRENT_DATE()interval 14 days) then ‘next 14 days’
when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 21 days) then ‘next 21 days’
else 'Over 21 days'
end
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2 -
I think you are missing a comma in the DATE_ADD statement. Try changing
DATE_ADD(CURRENT_DATE()interval 3 days)
to:
DATE_ADD(CURRENT_DATE(), interval 3 days)
Also I'm not sure why you have so many case/end statements? I use the following block of code for date buckets, and it works fine else-chaining without the Else/Case between, thought you might want to see to possibly shorten your code.
Case when DATEDIFF(`Event Date`,`Boxed Date`) < 0 then 'Prior to Box Date' when DATEDIFF(`Event Date`,`Boxed Date`) < 91 then 'Under 3 Months' when DATEDIFF(`Event Date`,`Boxed Date`) < 181 then '3-6 Months' when DATEDIFF(`Event Date`,`Boxed Date`) < 271 then '6-9 Months' when DATEDIFF(`Event Date`,`Boxed Date`) < 366 then '9-12 Months' when DATEDIFF(`Event Date`,`Boxed Date`) > 365 then 'Over 12 Months' when `Event Date`is null then 'Production' else 'Unknown Timeframe Error' end
4
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 308 Workbench
- 7 Cloud Amplifier
- 10 Federated
- 3.8K Transform
- 660 Datasets
- 117 SQL DataFlows
- 2.2K Magic ETL
- 818 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 84 App Studio
- 46 Variables
- 781 Automate
- 191 Apps
- 483 APIs & Domo Developer
- 84 Workflows
- 23 Code Engine
- 43 AI and Machine Learning
- 22 AI Chat
- 3 AI Projects and Models
- 18 Jupyter Workspaces
- 409 Distribute
- 116 Domo Everywhere
- 282 Scheduled Reports
- 11 Software Integrations
- 146 Manage
- 142 Governance & Security
- 8 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 115 Community Announcements
- 4.8K Archive