An issue has occurred during processing. We are unable to complete the request at this time.
Hi I am new to Domo and have no SQL knowledge. I am trying to get our YTD cases quantity with some condition taken from other working beast mode. If someone can please help me figure the beast mode below why it's erroring. Thank you ion advance.
SUM(CASE WHEN `Year`=2020 AND `Month #` <=`Month # for Today`
WHEN `Unit price`>100 THEN ((`SO Ordered Qty`)*`Cases per Pallet`)
ELSE (`SO Ordered Qty`)
I would get rid of your outer SUM function as I think it not necessary since you are already summing inside your case statement. I would suggest this:
CASE WHEN `Year`=2020 AND `Month #` <=`Month # for Today` THEN (SUM (CASE WHEN `Unit price`>100 THEN (`SO Ordered Qty`*`Cases per Pallet`) ELSE `SO Ordered Qty` END) ) ELSE 0 END**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
For your specific beast mode it's failing because you're attempting to aggregate on top of an aggregate which isn't possible. When starting a problem like this it's good practice to start breaking these out into smaller chunks to validate the data as you're working through it.
First thing you'll want to do is instead of hard coding 2020 in your beast mode you're wanting the current year. As is it will break when January 2021 comes around and you'd need to keep updating this beast mode every year.
So instead of 2020 you can get the year of the current date:
Depending on how you're wanting to display this information depends on how to solve your problem. If you're just getting a text card with a single value or using at a summary number this should work:
Essentially this is combining your filter conditions into a single CASE statement instead of trying to do sum of sum twice.
If you're wanting a running YTD total then you'll need to utilize a Window Function (this requires a feature switch turned on. Talk to your CSM if you don't have it enabled yet).
SUM(SUM(CASE WHEN `Unit price`>100 THEN (`SO Ordered Qty`)*`Cases per Pallet` ELSE (`SO Ordered Qty`) END )) OVER (PARTITION BY `Year`)
(these are all untested code examples but should work)**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
- 7.6K All Categories
- 913 Connectors
- 241 Workbench
- 470 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 445 Datasets
- 24 Visualize
- 194 Beast Mode
- 2K Charting
- 6 Variables
- 14 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 14 Manage
- 35 Governance & Security
- 18 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 15 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部