Beastmode | Exclude data based on dates
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.
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 to any 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' endJae 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
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 472 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 198 Visualize
- 254 Beast Mode
- 2.1K Charting
- 11 Variables
- 82 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 178 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive