Unable to Create Same Time vs Yr Ago
I'm trying to create a dynamic Comparison Value that adjusts the view based on Time Period Selection. I was able to create a Gauge Value that works, but am struggle with the Beast Mode that would allow the end user to view vs same time year ago value.
I have the code below and have tried various adjustments, but keep getting a syntax error from Domo (which isn't helpful at all).
CASE
WHEN Time Period Selection: = '4' AND WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 4 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
WHEN Time Period Selection: = '13' AND WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 13 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
WHEN Time Period Selection: = '26' AND WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 26 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
WHEN Time Period Selection: = '52' AND WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 52 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
WHEN Time Period Selection: = 'YTD' AND YEAR(WEEK_STARTING) = YEAR(CURRENT_DATE()) - 1 AND WEEK_STARTING BETWEEN DATE_TRUNC('YEAR', CURRENT_DATE() - INTERVAL 1 YEAR) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
ELSE 0
END
Best Answer
-
Just to be clear, what I posted is not the full Beast Mode, just what I suggested to replace the BETWEEN condition inside of each of the WHEN statements and not meant to be entered on it's own.
I'd guess you're using the
Multi-Value Columns
Chart Type, if so what does your source dataset looks like, for this to work you'll need to aggregate your data accordingly, most likely using the FIXED function since you'll need to include the full dataset and then limit what gets counted for on each case, but without knowing what your Dataset looks like it's hard to make any suggestions.0
Answers
-
I try not to use BETWEEN as I seem to never get it right, try replacing BETWEEN with two comparisons something like:
WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 4 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
Replaced by
WEEK_STARTING >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 4 WEEK) AND WEEK_STARTING <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
Perhaps that will help solving the syntax error you're getting.
1 -
Typically when I'm doing period over period analysis I'll restructure my data to make things like this easier. I've outlined how to do this here:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
This also gave a syntax error. Is this something Domo can do? I'm relatively new to the platform so not sure of the limitations
0 -
This would be great if possible but it's not something we can do.
0 -
Just to be clear, what I posted is not the full Beast Mode, just what I suggested to replace the BETWEEN condition inside of each of the WHEN statements and not meant to be entered on it's own.
I'd guess you're using the
Multi-Value Columns
Chart Type, if so what does your source dataset looks like, for this to work you'll need to aggregate your data accordingly, most likely using the FIXED function since you'll need to include the full dataset and then limit what gets counted for on each case, but without knowing what your Dataset looks like it's hard to make any suggestions.0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive