Count of ID when condition is met
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
Answers
-
Beastmodes are case sensitive. I believe you just need to change 'Active' to 'ACTIVE'
If I solved your problem, please select "yes" above
0 -
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.
0 -
Hi @Armin ,
So when you run this, what is the result you see and why is it incorrect?
John Le
You're only one dashboard away.
Click here for more video solutions:
0 -
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
0 -
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 ACTIVE0 -
@ColemenWilson It's just a table, with the IDs, customer status and I want to add the "Active Count" as a column.
0 -
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
0 -
Recreated it in Excel, Columns A, B, C are given and I want to create Column D
0 -
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?
John Le
You're only one dashboard away.
Click here for more video solutions:
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive