Beastmode | Exclude data based on dates

user046467

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.

GrantSmith

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?

MarkSnodgrass

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)

jaeW_at_Onyx

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