Beast Mode for Count Distinct with Condition

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



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

The above give me a syntax error.


Trying with 'Case':

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

else '0'


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


Any help would be appreciated.

Best Answer

  • Unknown
    Answer ✓

    Hi, user03295,


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

    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()


  • That worked like a charm ! ? Thank you 

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



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

  • That worked out! Thank you very much.