Count of ID when condition is met

Armin · April 22

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

ColemenWilson · April 22

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

Armin · April 22

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 · April 22

Hi @Armin ,

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

ColemenWilson · April 22

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

Armin · April 22

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 · April 22

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

ColemenWilson · April 22

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 · April 22

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

DashboardDude · April 22

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?

Count of ID when condition is met

Answers

Categories