Multiple filters in dataset with AND & OR conditions
Name | City | Troops | Support |
A | XYZ | 10 | Y |
B | XYZ | 30 | N |
C | PQR | 20 | Y |
D | PQR | 40 | N |
E | MNO | 30 | Y |
F | MNO | 10 | N |
A | MNO | 20 | Y |
B | PQR | 40 | N |
C | XYZ | 25 | Y |
D | MNO | 35 | N |
E | PQR | 45 | Y |
F | XYZ | 5 | N |
A | PQR | 40 | Y |
B | XYZ | 25 | N |
C | MNO | 35 | Y |
D | PQR | 45 | N |
E | XYZ | 5 | Y |
F | MNO | 20 | N |
My dataset looks something like this with lot many rows. I want to find the sum of troops A has with Support (Support is Y) and for only XYZ and MNO Cities.
I have tried something like this
Case
when support = y then
case when city = MNO or city = XYZ then
troops
end
end
I use both or & and at the red colored portion above but I was not able to get the required number.
Please help me
Comments
-
I'm not sure what card you are wanting to use to display your result, but have you tried using the filters to only show the items you are looking for? You shouldn't even need to do an elaborate CASE statement.
Based on what you said, I would drag Name into the filters section and select A. I would also drag Support into the filters and select Y. Finally, drag City into the filter and select XYZ and MNO. You could then use something like a single value card and drag Troops into the value and choose SUM for your aggregation. This should get you what you are looking for.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
If you're having trouble with the OR, then you can break city MNO and city XYZ into separate lines in the CASE statement. You can do something like this:
case
when `City` = 'MNO' and `Support` = 'Y' then `Troops`
when `City` = 'XYZ' and `Support` = 'Y' then `Troops`
else 0 end0 -
In my experience when using AND & OR conditions parenthasis are key, I would write it like this:
case
when (support=y and (city=MNO or city=XYZ)) then Troops
end0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive