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.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
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 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