Beastmode | Exclude data based on dates

Hi all,
I'm probably missing something small here but I'm using the following beastmode:
(case when `Date` >2021-07-01 then (case when `company` like '%company1%' and `ConversionTypeName` like '%product1%' then 'exclude' else 'include' end) end)
But when I use it as a filter it excludes everything and not only the sales for 'product1' after 2021-07-01
Hopefully someone can point me in the right direction.
Comments
-
Hi @user046467
Are you filtering for 'include' values or excluding 'exclude' values?
If you're checking for only include values anything prior to 2021-07-01 won't be found because you don't have an else clause to mark it as include (it's returning NULL). How do you want this to behave for records prior to 2021-07-01? Should they all be ignored? Should they be included along with company1 and product1 records?
Have you tried putting your beast mode into a table along with the date, company and conversiontypename fields to make sure the logic is correct? Have you tried breaking down your logic into separate beast modes to determine which part might be incorrect?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
When evaluating a date, your date needs to be in single quotes. I would also suggest to have something in your outer case clause as @GrantSmith suggests. I'm guessing you would want to exclude those, so I have re-written it with that in mind.
(case when `Date` > '2021-07-01' then (case when `company` like '%company1%' and `ConversionTypeName` like '%product1%' then 'exclude' else 'include' end) else 'exclude' end)
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Clean up your SQL and avoid using nested CASE statements. you want your code to read cleanly.
case when `Date` > '2021-07-01' AND `company` like '%company1%' AND `ConversionTypeName` like '%product1%' then 'exclude' else 'include' end
FYI it is syntactically correct to nest your logic using parenthesis, but again... avoid nested CASE.
case when `Date` > '2021-07-01' AND (`company` like '%company1%' OR `ConversionTypeName` like '%product1%' then 'exclude' ) else 'include' end
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"2
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 656 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 811 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 80 App Studio
- 45 Variables
- 771 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 77 Workflows
- 23 Code Engine
- 36 AI and Machine Learning
- 19 AI Chat
- AI Playground
- AI Projects and Models
- 17 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 142 Manage
- 138 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive