Beast Mode - Count Case When with multiple criteria

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 Portland, Oregon 🔴

    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)

  • GrantSmith
    GrantSmith Indiana 🔴

    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)
    
  • Shumilex
    Shumilex ⚪️

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

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    ... 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