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.2K Product Ideas
 1.2K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 273 Workbench
 2 Cloud Amplifier
 3 Federated
 2.7K Transform
 78 SQL DataFlows
 526 Datasets
 2.1K Magic ETL
 3K Visualize
 2.2K Charting
 441 Beast Mode
 23 Variables
 514 Automate
 115 Apps
 391 APIs & Domo Developer
 8 Workflows
 26 Predict
 10 Jupyter Workspaces
 16 R & Python Tiles
 332 Distribute
 77 Domo Everywhere
 255 Scheduled Reports
 67 Manage
 67 Governance & Security
 1 Product Release Questions
 Community Forums
 41 Getting Started
 27 Community Member Introductions
 68 Community Announcements
 4.8K Archive