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!**
  • 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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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"