Count of ID when condition is met

Armin · 2024-04-22T15:10:03+00:00

There was an error rendering this rich post.

Armin

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

Find more posts tagged with

Count

Beast Mode

Accepted answers

All comments

ColemenWilson

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

Armin

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

Hi @Armin ,

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

ColemenWilson

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

Armin

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

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

ColemenWilson

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.

Armin

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

DashboardDude

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?