Beast Mode for Count Distinct with Condition

Akhil
Akhil
in Dataflows

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.

Best Answer

  • Unknown
    Unknown
    Answer ✓

    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
    )

Answers

  • Akhil
    Akhil

    That worked like a charm ! ? Thank you 

  • hamza_123
    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
    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
    hamza_123 🟡

    That worked out! Thank you very much. 

Categories