Beast Mode - Count Case When with multiple criteria

Shumilex
Shumilex Member
in Charting

HI,

Trying to use the below to count when both criteria are met. If an agent has a record for a specific date count 1.


(CASE WHEN (COUNT(DISTINCT `ReportDate`)) >= '1' and (COUNT(DISTINCT `Agents`)) >= '1' THEN '1' ELSE ? END)


It is not allowing me to calculate this. Can someone please assist?

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    I might be able to make better recommendations if you provided a sample of your data, but it is likely giving an error because of the quotes around 1 after your greater than or equal to. Try this for starters:

    (CASE WHEN (COUNT(DISTINCT `ReportDate`)) >= 1 and (COUNT(DISTINCT `Agents`)) >= 1 THEN '1' ELSE ? 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.
  • GrantSmith
    GrantSmith Coach

    Have you tried breaking down your case statement into multiple beast modes to help diagnose which portion of your beast mode is having issues?


    Also just looking at your code you need the question mark to be surrounded by quotes:

    (CASE WHEN (COUNT(DISTINCT `ReportDate`)) >= 1 and (COUNT(DISTINCT `Agents`)) >= 1 THEN '1' ELSE '?' END)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Shumilex
    Shumilex Member

    Hi Guys, thanks, I noticed the missing quotes afterwards an it did work. 🤦

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    ... you're consistently making the same decision to nest COUNT DISTINCT inside CASE statements. Be very careful, this is a bad habit to get into and can yield unexpected results.



    I suspect you have a data interpretation problem.

    quick question. you're doing count distinct of report date and agent ... but is your count distinct supposed to be applied to the SAME agent? is Agent on the axis?

    OR IF you already have Agent on the axis THEN the second CASE statement is a red herring because it is guaranteed to always be 1.


    If you actually 'need' Count Distinct', you might consider restructuring your data to also report the empty spaces (days when the agent did not have activity).

    https://www.youtube.com/watch?v=Xb4QgKYgaqg&t=178s

    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"

Categories