Syntac Error: Trying to group days into weeks using Case Function
Hi, I've been trying to group days to represent buckets of weeks. I code I've used is as follows:
Case
when `accepted_date` between '01/01/2018' and '09/06/2019'
then '1 Wk Sp'
when `accepted_date` between '09/07/2019' and '09/13/2019'
then '2 Wk Sp'
when `accepted_date` between '09/14/2019' and '09/20/2019'
then '3 Wk Sp'
when `accepted_date` between '09/21/2019' and '09/27/2019'
then '4 Wk Sp'
when `accepted_date` between '09/28/2019' and '10/04/2019'
then '1 Wk Oct'
when `accepted_date` between '10/05/2019' and '10/11/2019'
then '2 Wk Oct'
when `accepted_date` between '`10/12/2019' and '10/18/2019'
then '3 Wk Oct'
when `accepted_date` between '10/19/2019' and '10/25/2019'
then '4 Wk Oct'
when `accepted_date` between '10/26/2019' and '11/01/2019'
then '5 Wk Oct'
when `accepted_date` between '11/02/2019' and '11/08/2019'
then '1 Wk Nv'
when `accepted_date` between '11/09/2019' and '11/15/2019'
then '2 Wk Nv'
when `accepted_date` between '11/16/2019' and '11/22/2019'
then '3 Wk Nv'
when `accepted_date` between '11/23/2019' and '11/29/2019'
then '4 Wk Nv'
when `accepted_date` between '11/30/2019' and '12/06/2019'
Then '1 Wk Dc'
when `accepted_date` between '12/07/2019' and '12/13/2019'
Then '2 Wk Dc'
when `accepted_date` between '12/14/2019' and '12/20/2019'
Then '3 Wk Dc'
when `accepted_date` between '12/21/2019' and '12/27/2019'
Then '4 Wk Dc'
when `accepted_date` between '12/28/2019' and '01/03/2020'
Then '1 Wk Ja'
when `accepted_date` between '01/04/2020’ and ’01/10/2020'
Then '2 Wk Ja'
when `accepted_date` between '01/11/2020' and '01/17/2020'
Then '3 Wk Ja'
when `accepted_date` between '01/18/2020' and '01/24/2020'
Then '4 Wk Ja'
when `accepted_date` between '01/25/2020' and '01/31/2020'
Then '1 Wk Fb'
when `accepted_date` between '02/01/2020' and '02/07/2020'
then '2 Wk Fb'
when `accepted_date` between '02/08/2020' and '02/14/2020'
Then '3 Wk Fb'
when `accepted_date` between '02/15/2019' and '02/21/2019'
Then '4 Wk Fb'
when `accepted_date` between '02/22/2020' and '02/28/2020'
Then '1 Wk Mr'
when `accepted_date` between '02/29/2020' and '03/06/2020'
then '2 Wk Mr'
when `accepted_date` between '03/07/2020' and '03/13/2020'
then '3 Wk Mr'
when `accepted_date` between '03/14/2020' and '03/20/2020'
Then '4 Wk Mr'
when `accepted_date` between '03/21/2020' and '03/27/2020'
Then '5 Wk Mr'
when `accepted_date` between '03/28/2020' and '04/03/2020'
Then '1 Wk Ap'
when `accepted_date` between '04/04/2020' and '04/10/2020'
then '2 Wk Ap'
when `accepted_date` between '04/11/2020' and '04/17/2020'
then '3 Wk Ap'
when `accepted_date` between '04/18/2020' and '04/24/2020'
then '4 Wk Ap'
when `accepted_date` between '04/25/2020' and '05/01/2020'
then '1 Wk My'
when `accepted_date` between '05/02/2020' and '05/08/2020'
Then '2 Wk My'
when `accepted_date` between '05/09/2020' and '05/15/2020'
then '3 Wk My'
when `accepted_date` between '05/16/2020' and '05/22/2020'
then '4 Wk My'
when `accepted_date` between '05/23/2020' and '05/29/2020'
then '1 Wk Jn'
when `accepted_date` between '05/30/2020' and '06/05/2020'
then '2 Wk Jn'
when `accepted_date` between '06/06/2020' and '06/12/2020'
then '3 Wk Jn'
when `accepted_date` between '06/13/2020' and '06/19/2020'
then '4 Wk Jn'
when `accepted_date` between '06/20/2020' and '06/26/2020'
then '5 Wk Jn'
when `accepted_date` between '06/27/2020' and '07/03/2020'
then '1 Wk Jy'
when `accepted_date` between '07/04/2020' and '07/10/2020'
then '2 Wk Jy'
when `accepted_date` between '07/11/2020' and '07/17/2020'
then '3 Wk Jy'
when `accepted_date` between '07/18/2020' and '07/24/2020'
then '4 Wk Jy'
when `accepted_date` between '07/25/2020' and '08/01/2020'
then '1 Wk Ag'
when `accepted_date` between '08/02/2020' and '08/07/2020'
then '2 Wk Ag'
when `accepted_date` between '08/08/2020' and '08/14/2020'
then '3 Wk Ag'
when `accepted_date` between '08/15/2020' and '08/21/2020'
then '4 Wk Ag'
when `accepted_date` between '08/22/2020' and '08/28/2020'
then '5 Wk Ag'
End
Please let me know why this code is showing a syntax error in beast mode.
Best Answer

Beast modes don't support BETWEEN syntax.
But also... this is the sort of thing that might benefit from a lookup table / date dimension + a fusion. It'll be more easy to manage.
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"1
Answers

Beast modes don't support BETWEEN syntax.
But also... this is the sort of thing that might benefit from a lookup table / date dimension + a fusion. It'll be more easy to manage.
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"1
Categories
 All Categories
 1.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 283 Workbench
 4 Cloud Amplifier
 4 Federated
 2.7K Transform
 90 SQL DataFlows
 569 Datasets
 2K Magic ETL
 3.4K Visualize
 2.4K Charting
 601 Beast Mode
 15 App Studio
 29 Variables
 600 Automate
 151 Apps
 418 APIs & Domo Developer
 29 Workflows
 2 DomoAI
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 362 Distribute
 99 Domo Everywhere
 261 Scheduled Reports
 2 Software Integrations
 98 Manage
 95 Governance & Security
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 90 Community Announcements
 4.8K Archive