Beast Mode for Count Distinct with Condition

Akhil · September 2018

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.

Unknown · September 2018

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
)

Unknown · September 2018

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 · September 2018

That worked like a charm ! ? Thank you

hamza_123 · August 2019

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 · August 2019

@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 · August 2019

That worked out! Thank you very much.

Beast Mode for Count Distinct with Condition

Best Answer

Answers

Categories