Beast Mode: Nested Case Statement Help
Hey, I'm trying to select week id base on the current day and I wrote below case statement. But I'm getting error in here. Can someone help?
(CASE
WHEN week_id <= (
CASE
WHEN CURRENT_DATE() BETWEEN '2024-02-10' AND '2024-02-16' THEN 1
WHEN CURRENT_DATE() BETWEEN '2024-02-17' AND '2024-02-23' THEN 2
WHEN CURRENT_DATE() BETWEEN '2024-02-24' AND '2024-03-01' THEN 3
WHEN CURRENT_DATE() BETWEEN '2024-03-02' AND '2024-03-08' THEN 4
WHEN CURRENT_DATE() BETWEEN '2024-03-09' AND '2024-03-15' THEN 5
WHEN CURRENT_DATE() BETWEEN '2024-03-16' AND '2024-03-22' THEN 6
WHEN CURRENT_DATE() BETWEEN '2024-03-23' AND '2024-03-29' THEN 7
WHEN CURRENT_DATE() BETWEEN '2024-03-30' AND '2024-04-05' THEN 8
WHEN CURRENT_DATE() BETWEEN '2024-04-06' AND '2024-04-12' THEN 9
WHEN CURRENT_DATE() BETWEEN '2024-04-13' AND '2024-04-19' THEN 10
WHEN CURRENT_DATE() BETWEEN '2024-04-20' AND '2024-04-26' THEN 11
WHEN CURRENT_DATE() BETWEEN '2024-04-27' AND '2024-05-03' THEN 12
WHEN CURRENT_DATE() BETWEEN '2024-05-04' AND '2024-05-10' THEN 13
WHEN CURRENT_DATE() BETWEEN '2024-05-11' AND '2024-05-17' THEN 14
WHEN CURRENT_DATE() BETWEEN '2024-05-18' AND '2024-05-24' THEN 15
WHEN CURRENT_DATE() BETWEEN '2024-05-25' AND '2024-05-31' THEN 16
WHEN CURRENT_DATE() BETWEEN '2024-06-01' AND '2024-06-07' THEN 17
WHEN CURRENT_DATE() BETWEEN '2024-06-08' AND '2024-06-14' THEN 18
WHEN CURRENT_DATE() BETWEEN '2024-06-15' AND '2024-06-21' THEN 19
WHEN CURRENT_DATE() BETWEEN '2024-06-22' AND '2024-06-28' THEN 20
WHEN CURRENT_DATE() BETWEEN '2024-06-29' AND '2024-07-05' THEN 21
WHEN CURRENT_DATE() BETWEEN '2024-07-06' AND '2024-07-12' THEN 22
WHEN CURRENT_DATE() BETWEEN '2024-07-13' AND '2024-07-19' THEN 23
WHEN CURRENT_DATE() BETWEEN '2024-07-20' AND '2024-07-26' THEN 24
WHEN CURRENT_DATE() BETWEEN '2024-07-27' AND '2024-08-02' THEN 25
WHEN CURRENT_DATE() BETWEEN '2024-08-03' AND '2024-08-09' THEN 26
WHEN CURRENT_DATE() BETWEEN '2024-08-10' AND '2024-08-16' THEN 27
WHEN CURRENT_DATE() BETWEEN '2024-08-17' AND '2024-08-23' THEN 28
WHEN CURRENT_DATE() BETWEEN '2024-08-24' AND '2024-08-30' THEN 29
WHEN CURRENT_DATE() BETWEEN '2024-08-31' AND '2024-09-06' THEN 30
WHEN CURRENT_DATE() BETWEEN '2024-09-07' AND '2024-09-13' THEN 31
WHEN CURRENT_DATE() BETWEEN '2024-09-14' AND '2024-09-20' THEN 32
WHEN CURRENT_DATE() BETWEEN '2024-09-21' AND '2024-09-27' THEN 33
WHEN CURRENT_DATE() BETWEEN '2024-09-28' AND '2024-10-04' THEN 34
WHEN CURRENT_DATE() BETWEEN '2024-10-05' AND '2024-10-11' THEN 35
WHEN CURRENT_DATE() BETWEEN '2024-10-12' AND '2024-10-18' THEN 36
WHEN CURRENT_DATE() BETWEEN '2024-10-19' AND '2024-10-25' THEN 37
WHEN CURRENT_DATE() BETWEEN '2024-10-26' AND '2024-11-01' THEN 38
WHEN CURRENT_DATE() BETWEEN '2024-11-02' AND '2024-11-08' THEN 39
WHEN CURRENT_DATE() BETWEEN '2024-11-09' AND '2024-11-15' THEN 40
WHEN CURRENT_DATE() BETWEEN '2024-11-16' AND '2024-11-22' THEN 41
WHEN CURRENT_DATE() BETWEEN '2024-11-23' AND '2024-11-29' THEN 42
WHEN CURRENT_DATE() BETWEEN '2024-11-30' AND '2024-12-06' THEN 43
WHEN CURRENT_DATE() BETWEEN '2024-12-07' AND '2024-12-13' THEN 44
WHEN CURRENT_DATE() BETWEEN '2024-12-14' AND '2024-12-20' THEN 45
WHEN CURRENT_DATE() BETWEEN '2024-12-21' AND '2024-12-27' THEN 46
WHEN CURRENT_DATE() BETWEEN '2024-12-28' AND '2025-01-03' THEN 47
WHEN CURRENT_DATE() BETWEEN '2025-01-04' AND '2025-01-10' THEN 48
WHEN CURRENT_DATE() BETWEEN '2025-01-11' AND '2025-01-17' THEN 49
WHEN CURRENT_DATE() BETWEEN '2025-01-18' AND '2025-01-24' THEN 50
WHEN CURRENT_DATE() BETWEEN '2025-01-25' AND '2025-01-31' THEN 51
WHEN CURRENT_DATE() BETWEEN '2025-02-01' AND '2025-02-07' THEN 52
ELSE 53
END)
THEN 1
ELSE 0
END)
0
Best Answer
-
The error in the provided Domo code lies in the mismatched parentheses at the end of the CASE statement. You have an extra ) before the THEN 1 clause.
(CASE
WHEN week_id <= (
CASE
WHEN CURRENT_DATE() BETWEEN '2024-02-10' AND '2024-02-16' THEN 1
WHEN CURRENT_DATE() BETWEEN '2024-02-17' AND '2024-02-23' THEN 2
WHEN CURRENT_DATE() BETWEEN '2024-02-24' AND '2024-03-01' THEN 3
WHEN CURRENT_DATE() BETWEEN '2024-03-02' AND '2024-03-08' THEN 4
WHEN CURRENT_DATE() BETWEEN '2024-03-09' AND '2024-03-15' THEN 5
WHEN CURRENT_DATE() BETWEEN '2024-03-16' AND '2024-03-22' THEN 6
WHEN CURRENT_DATE() BETWEEN '2024-03-23' AND '2024-03-29' THEN 7
WHEN CURRENT_DATE() BETWEEN '2024-03-30' AND '2024-04-05' THEN 8
WHEN CURRENT_DATE() BETWEEN '2024-04-06' AND '2024-04-12' THEN 9
WHEN CURRENT_DATE() BETWEEN '2024-04-13' AND '2024-04-19' THEN 10
WHEN CURRENT_DATE() BETWEEN '2024-04-20' AND '2024-04-26' THEN 11
WHEN CURRENT_DATE() BETWEEN '2024-04-27' AND '2024-05-03' THEN 12
WHEN CURRENT_DATE() BETWEEN '2024-05-04' AND '2024-05-10' THEN 13
WHEN CURRENT_DATE() BETWEEN '2024-05-11' AND '2024-05-17' THEN 14
WHEN CURRENT_DATE() BETWEEN '2024-05-18' AND '2024-05-24' THEN 15
WHEN CURRENT_DATE() BETWEEN '2024-05-25' AND '2024-05-31' THEN 16
WHEN CURRENT_DATE() BETWEEN '2024-06-01' AND '2024-06-07' THEN 17
WHEN CURRENT_DATE() BETWEEN '2024-06-08' AND '2024-06-14' THEN 18
WHEN CURRENT_DATE() BETWEEN '2024-06-15' AND '2024-06-21' THEN 19
WHEN CURRENT_DATE() BETWEEN '2024-06-22' AND '2024-06-28' THEN 20
WHEN CURRENT_DATE() BETWEEN '2024-06-29' AND '2024-07-05' THEN 21
WHEN CURRENT_DATE() BETWEEN '2024-07-06' AND '2024-07-12' THEN 22
WHEN CURRENT_DATE() BETWEEN '2024-07-13' AND '2024-07-19' THEN 23
WHEN CURRENT_DATE() BETWEEN '2024-07-20' AND '2024-07-26' THEN 24
WHEN CURRENT_DATE() BETWEEN '2024-07-27' AND '2024-08-02' THEN 25
WHEN CURRENT_DATE() BETWEEN '2024-08-03' AND '2024-08-09' THEN 26
WHEN CURRENT_DATE() BETWEEN '2024-08-10' AND '2024-08-16' THEN 27
WHEN CURRENT_DATE() BETWEEN '2024-08-17' AND '2024-08-23' THEN 28
WHEN CURRENT_DATE() BETWEEN '2024-08-24' AND '2024-08-30' THEN 29
WHEN CURRENT_DATE() BETWEEN '2024-08-31' AND '2024-09-06' THEN 30
WHEN CURRENT_DATE() BETWEEN '2024-09-07' AND '2024-09-13' THEN 31
WHEN CURRENT_DATE() BETWEEN '2024-09-14' AND '2024-09-20' THEN 32
WHEN CURRENT_DATE() BETWEEN '2024-09-21' AND '2024-09-27' THEN 33
WHEN CURRENT_DATE() BETWEEN '2024-09-28' AND '2024-10-04' THEN 34
WHEN CURRENT_DATE() BETWEEN '2024-10-05' AND '2024-10-11' THEN 35
WHEN CURRENT_DATE() BETWEEN '2024-10-12' AND '2024-10-18' THEN 36
WHEN CURRENT_DATE() BETWEEN '2024-10-19' AND '2024-10-25' THEN 37
WHEN CURRENT_DATE() BETWEEN '2024-10-26' AND '2024-11-01' THEN 38
WHEN CURRENT_DATE() BETWEEN '2024-11-02' AND '2024-11-08' THEN 39
WHEN CURRENT_DATE() BETWEEN '2024-11-09' AND '2024-11-15' THEN 40
WHEN CURRENT_DATE() BETWEEN '2024-11-16' AND '2024-11-22' THEN 41
WHEN CURRENT_DATE() BETWEEN '2024-11-23' AND '2024-11-29' THEN 42
WHEN CURRENT_DATE() BETWEEN '2024-11-30' AND '2024-12-06' THEN 43
WHEN CURRENT_DATE() BETWEEN '2024-12-07' AND '2024-12-13' THEN 44
WHEN CURRENT_DATE() BETWEEN '2024-12-14' AND '2024-12-20' THEN 45
WHEN CURRENT_DATE() BETWEEN '2024-12-21' AND '2024-12-27' THEN 46
WHEN CURRENT_DATE() BETWEEN '2024-12-28' AND '2025-01-03' THEN 47
WHEN CURRENT_DATE() BETWEEN '2025-01-04' AND '2025-01-10' THEN 48
WHEN CURRENT_DATE() BETWEEN '2025-01-11' AND '2025-01-17' THEN 49
WHEN CURRENT_DATE() BETWEEN '2025-01-18' AND '2025-01-24' THEN 50
WHEN CURRENT_DATE() BETWEEN '2025-01-25' AND '2025-01-31' THEN 51
WHEN CURRENT_DATE() BETWEEN '2025-02-01' AND '2025-02-07' THEN 52
ELSE 53
END)
THEN 1
ELSE 0
END)** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Answers
-
The error in the provided Domo code lies in the mismatched parentheses at the end of the CASE statement. You have an extra ) before the THEN 1 clause.
(CASE
WHEN week_id <= (
CASE
WHEN CURRENT_DATE() BETWEEN '2024-02-10' AND '2024-02-16' THEN 1
WHEN CURRENT_DATE() BETWEEN '2024-02-17' AND '2024-02-23' THEN 2
WHEN CURRENT_DATE() BETWEEN '2024-02-24' AND '2024-03-01' THEN 3
WHEN CURRENT_DATE() BETWEEN '2024-03-02' AND '2024-03-08' THEN 4
WHEN CURRENT_DATE() BETWEEN '2024-03-09' AND '2024-03-15' THEN 5
WHEN CURRENT_DATE() BETWEEN '2024-03-16' AND '2024-03-22' THEN 6
WHEN CURRENT_DATE() BETWEEN '2024-03-23' AND '2024-03-29' THEN 7
WHEN CURRENT_DATE() BETWEEN '2024-03-30' AND '2024-04-05' THEN 8
WHEN CURRENT_DATE() BETWEEN '2024-04-06' AND '2024-04-12' THEN 9
WHEN CURRENT_DATE() BETWEEN '2024-04-13' AND '2024-04-19' THEN 10
WHEN CURRENT_DATE() BETWEEN '2024-04-20' AND '2024-04-26' THEN 11
WHEN CURRENT_DATE() BETWEEN '2024-04-27' AND '2024-05-03' THEN 12
WHEN CURRENT_DATE() BETWEEN '2024-05-04' AND '2024-05-10' THEN 13
WHEN CURRENT_DATE() BETWEEN '2024-05-11' AND '2024-05-17' THEN 14
WHEN CURRENT_DATE() BETWEEN '2024-05-18' AND '2024-05-24' THEN 15
WHEN CURRENT_DATE() BETWEEN '2024-05-25' AND '2024-05-31' THEN 16
WHEN CURRENT_DATE() BETWEEN '2024-06-01' AND '2024-06-07' THEN 17
WHEN CURRENT_DATE() BETWEEN '2024-06-08' AND '2024-06-14' THEN 18
WHEN CURRENT_DATE() BETWEEN '2024-06-15' AND '2024-06-21' THEN 19
WHEN CURRENT_DATE() BETWEEN '2024-06-22' AND '2024-06-28' THEN 20
WHEN CURRENT_DATE() BETWEEN '2024-06-29' AND '2024-07-05' THEN 21
WHEN CURRENT_DATE() BETWEEN '2024-07-06' AND '2024-07-12' THEN 22
WHEN CURRENT_DATE() BETWEEN '2024-07-13' AND '2024-07-19' THEN 23
WHEN CURRENT_DATE() BETWEEN '2024-07-20' AND '2024-07-26' THEN 24
WHEN CURRENT_DATE() BETWEEN '2024-07-27' AND '2024-08-02' THEN 25
WHEN CURRENT_DATE() BETWEEN '2024-08-03' AND '2024-08-09' THEN 26
WHEN CURRENT_DATE() BETWEEN '2024-08-10' AND '2024-08-16' THEN 27
WHEN CURRENT_DATE() BETWEEN '2024-08-17' AND '2024-08-23' THEN 28
WHEN CURRENT_DATE() BETWEEN '2024-08-24' AND '2024-08-30' THEN 29
WHEN CURRENT_DATE() BETWEEN '2024-08-31' AND '2024-09-06' THEN 30
WHEN CURRENT_DATE() BETWEEN '2024-09-07' AND '2024-09-13' THEN 31
WHEN CURRENT_DATE() BETWEEN '2024-09-14' AND '2024-09-20' THEN 32
WHEN CURRENT_DATE() BETWEEN '2024-09-21' AND '2024-09-27' THEN 33
WHEN CURRENT_DATE() BETWEEN '2024-09-28' AND '2024-10-04' THEN 34
WHEN CURRENT_DATE() BETWEEN '2024-10-05' AND '2024-10-11' THEN 35
WHEN CURRENT_DATE() BETWEEN '2024-10-12' AND '2024-10-18' THEN 36
WHEN CURRENT_DATE() BETWEEN '2024-10-19' AND '2024-10-25' THEN 37
WHEN CURRENT_DATE() BETWEEN '2024-10-26' AND '2024-11-01' THEN 38
WHEN CURRENT_DATE() BETWEEN '2024-11-02' AND '2024-11-08' THEN 39
WHEN CURRENT_DATE() BETWEEN '2024-11-09' AND '2024-11-15' THEN 40
WHEN CURRENT_DATE() BETWEEN '2024-11-16' AND '2024-11-22' THEN 41
WHEN CURRENT_DATE() BETWEEN '2024-11-23' AND '2024-11-29' THEN 42
WHEN CURRENT_DATE() BETWEEN '2024-11-30' AND '2024-12-06' THEN 43
WHEN CURRENT_DATE() BETWEEN '2024-12-07' AND '2024-12-13' THEN 44
WHEN CURRENT_DATE() BETWEEN '2024-12-14' AND '2024-12-20' THEN 45
WHEN CURRENT_DATE() BETWEEN '2024-12-21' AND '2024-12-27' THEN 46
WHEN CURRENT_DATE() BETWEEN '2024-12-28' AND '2025-01-03' THEN 47
WHEN CURRENT_DATE() BETWEEN '2025-01-04' AND '2025-01-10' THEN 48
WHEN CURRENT_DATE() BETWEEN '2025-01-11' AND '2025-01-17' THEN 49
WHEN CURRENT_DATE() BETWEEN '2025-01-18' AND '2025-01-24' THEN 50
WHEN CURRENT_DATE() BETWEEN '2025-01-25' AND '2025-01-31' THEN 51
WHEN CURRENT_DATE() BETWEEN '2025-02-01' AND '2025-02-07' THEN 52
ELSE 53
END)
THEN 1
ELSE 0
END)** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
Instead of a large case statement that explicitly assigns a week number to every day in the year, you should be able to express your week logic with some combination of YEARWEEK and INTERVAL arithmetic. I don't have enough information to know if this matches your logic well enough to work for future years, but this expression works for your 2024 weeks:
CASE WHEN 202400 + week_id <= YEARWEEK(CURRENT_DATE() - INTERVAL 41 DAY, 6) THEN 1 ELSE 0 ENDRandall Oveson <randall.oveson@domo.com>
0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 303 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 104 SQL DataFlows
- 640 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 769 Beast Mode
- 72 App Studio
- 43 Variables
- 718 Automate
- 185 Apps
- 462 APIs & Domo Developer
- 57 Workflows
- 14 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 402 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 9 Software Integrations
- 135 Manage
- 132 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive