Beast Mode - Count Case When with multiple criteria

Shumilex · 2021-07-22T17:48:03+00:00

There was an error rendering this rich post.

Shumilex

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?

Find more posts tagged with

Accepted answers

All comments

MarkSnodgrass

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

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

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

jaeW_at_Onyx

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