Count of ID when condition is met

Options
Armin
Armin Member
edited July 7 in Calculations & Variables (Beast Mode)

Hi guys,

I hope you are able to help me with the following situation.

Example:

Customer ID1 — Customer ID2 — Status — ..

ABC — DEF — ACTIVE

ABC — GHI — ACTIVE

LMN — OPQ — ACTIVE

LMN — RST — INACTIVE

Basically I have two IDs per customer, I now want to get a COUNT of "active per customer" but ONLY when Status = ACTIVE. My approach was the following: 

COUNT( 
 CASE WHEN `customerstatus` = 'Active' then `jigen_id`

  END

 )

But I don't get the results I was hoping for.

The results I'm hoping to achieve:

Customer ID1 = ABC shows Count = 2

Customer ID1 = LMN shows Count = 1

If there is any other solution to show me customers with more than one active, I'll take it as well. Doesn't have to be a count/case filter.

Thanks in advance,

cheers

Tagged:

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited April 2024

    Beastmodes are case sensitive. I believe you just need to change 'Active' to 'ACTIVE'

    If I solved your problem, please select "yes" above

  • Armin
    Armin Member

    I had tried it with Count Distinct but it doesn't work :(

    Also in my actual data the 'Active' matches so that's not it as well.

  • DashboardDude
    DashboardDude Coach

    Hi @Armin ,

    So when you run this, what is the result you see and why is it incorrect?

    Circle.png

    John Le

    Are you on my newsletter? If not, signup here so you don't miss out on my Domo tricks, alerts about my webinars, cooking tips and more

    Signup here:

    https://www.dashboarddudes.com/newsletter

  • ColemenWilson
    ColemenWilson Coach

    It would also be helpful to see what visualization you are trying to create and your expected view.

    If I solved your problem, please select "yes" above

  • Armin
    Armin Member

    So currently I have the following: 

    COUNT(DISTINCT
 CASE WHEN `customerstatus` = 'Active' then `Customer ID1` END
    
 )

    However for Customer ID1 "ABC" it shows 1 instead of 2 which I would like to see.

    If Customer "ABC" would have three with Active I'd like to see 3.
    In my data it actually is "Active" instead of ACTIVE

  • Armin
    Armin Member

    @ColemenWilson It's just a table, with the IDs, customer status and I want to add the "Active Count" as a column.

  • ColemenWilson
    ColemenWilson Coach
    edited April 2024

    You won't want to use count distinct then. Can you share what your visualization looks like? If you have both Customer ID1 and Customer ID2 as fields in the table it will not work as you are expecting.

    If I solved your problem, please select "yes" above

  • Armin
    Armin Member
    image.png

    Recreated it in Excel, Columns A, B, C are given and I want to create Column D

  • DashboardDude
    DashboardDude Coach

    Hi @Armin ,

    I'm trying to answer this one too, but struggling because I don't really understand the problem. If possible, can you show a screenshot of what you are seeing and why it's wrong? Like your formula works if you only show Customer ID 1 in a table with status, but won't work if you show customer ID 2 because the beast mode is aggregating.

    Like is this what you want to see?

    image.png
    Circle.png

    John Le

    Are you on my newsletter? If not, signup here so you don't miss out on my Domo tricks, alerts about my webinars, cooking tips and more

    Signup here:

    https://www.dashboarddudes.com/newsletter

Categories