Case Statement Help

leaflove
leaflove Member
in Charting

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)
image.png

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?

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    leaflove Member
    Answer ✓

    @MichelleH That worked! Thank you.

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    leaflove Member
    Answer ✓

    @MichelleH That worked! Thank you.

Categories