Case Statement Help

leaflove · 2024-01-09T21:47:35+00:00

There was an error rendering this rich post.

I'm trying to write a simple case statement, but it isn't working as intended. My data set has an attribute for Customer ID. When populated, this indicates that a customer was attached to a sale transaction. It populates with zero if no customer was attached. I want to count the unique customers that were attached, and exclude the fields populated with zero. Here's my current syntax:

(CASE 

  WHEN `customerID` > 0 THEN COUNT(DISTINCT `customerID`)
  
End)

Even with this Beast Mode, I believe it's still counting zero as a unique Customer ID, because all of my values have one additional number in the count tally. In the example image, the count tally is 5, when it should be 4. What am I doing wrong?

Find more posts tagged with

Accepted answers

MichelleH

@leaflove Beast modes generally work best when the aggregate function is outside of the case statement instead of inside like this:

count(distinct case when `customerID` > 0 then  `customerID`  end)

leaflove

@MichelleH That worked! Thank you.

All comments

MichelleH

@leaflove Beast modes generally work best when the aggregate function is outside of the case statement instead of inside like this:

count(distinct case when `customerID` > 0 then  `customerID`  end)

leaflove

@MichelleH That worked! Thank you.