Beast Mode for Count Distinct with Condition

Akhil

I want to get count of values in a column based on a condition for another column:

Example:

Count(Distinct 'visitor_id' ) where visitor_called = 'Y'

The above give me a syntax error.

Trying with 'Case':

(CASE
WHEN `visitor_called` = 'Y' then COUNT(DISTINCT `visitor_id`)

else '0'
END
)

This returns me overall counf for distinct visitor ID but not for condition specified.

Any help would be appreciated.

Find more posts tagged with

Accepted answers

unknown

Hi, user03295,

You very nearly had it right. Try wrapping your entire case statement in the count distinct function:

count(distinct 
  CASE
    WHEN `visitor_called` = 'Y' then `visitor_id`
    -- note I'm NOT using the "else" portion of the case statement. That's because, if `visitor_called` is not 'Y', then I want the case statement to return NULL. NULL is not included in aggregate functions like count()
    END
)

All comments

unknown

Hi, user03295,

You very nearly had it right. Try wrapping your entire case statement in the count distinct function:

count(distinct 
  CASE
    WHEN `visitor_called` = 'Y' then `visitor_id`
    -- note I'm NOT using the "else" portion of the case statement. That's because, if `visitor_called` is not 'Y', then I want the case statement to return NULL. NULL is not included in aggregate functions like count()
    END
)

Akhil

That worked like a charm ! ? Thank you

hamza_123

Hello.

If we didn't specify the visitor_called, but wanted to count all unique visitor_ids for each visitor_called, any idea how we could do that in Beast Mode?

Will be of great help. I'm going through a similar issue.

Akhil

@hamza_123 If my understanding is correct, your requirement is to see the count of distinct visitorIds for each available value of visitor_called (yes, no, etc.)

In that case, build a simple calculated metric with definition count(distinct visitor_ID) and apply it on the graph/table for the visitor_called.

Example: if on a horizontal bar graph,pull visitor_called on x-axis and calculated metric on y-axis.

Let me know if my understanding is incorrect. In that case, a sample of available data and required data would help.

hamza_123

That worked out! Thank you very much.