Please help me write this filter formula:

- How can I write a filter formula where I want to exclude everything that says 'INTERCOMPANY' from 'CUSTOMER TYPE' Column except this one scenario where we keep 'INTERCOMPANY' where there shows 'X' in 'COMPANY' Column.
Similarly, how to write a filter formula where I can exclude everything that says 'ENDUSER' from 'CUSTOMER TYPE' Column except where we keep 'INTERCOMPANY' where it shows 'A', B' & 'C' in 'COMPANY' Column.
Best Answers
-
While you can write formulas in the filter tile, for this type of logic, I would suggest adding a formula tile before your filter tile where you determine which rows to include or exclude and then you can just filter to include in your filter tile.
In your first scenario, your formula tile logic would look like this:
- case when CustomerType = 'INTERCOMPANY' AND Company <> ='X' THEN 'Exclude'
- ELSE 'Include' END
In the second scenario, it would look like this:
- case when CustomerType = 'ENDUSER' AND Company NOT IN ('A','B','C') THEN 'Exclude'
- ELSE 'Include' END
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.6 - case when CustomerType = 'INTERCOMPANY' AND Company <> ='X' THEN 'Exclude'
-
I think what you want is this:
- (
Customer Type
!= 'END') OR (Customer Type
= 'END' ANDBill-To Customer Name IN (
'A','B','C')
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 - (
-
In picture 1, I think you need to make it a nested case statement. The way case statements work, they exit once they find a condition that is true. A nested case statement would look like this:
- CASE WHEN condition1 THEN
- CASE WHEN condition2 THEN 'Include'
- ELSE 'Exclude'
- END
- ELSE 'Exclude'
- END
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 - CASE WHEN condition1 THEN
Answers
-
While you can write formulas in the filter tile, for this type of logic, I would suggest adding a formula tile before your filter tile where you determine which rows to include or exclude and then you can just filter to include in your filter tile.
In your first scenario, your formula tile logic would look like this:
- case when CustomerType = 'INTERCOMPANY' AND Company <> ='X' THEN 'Exclude'
- ELSE 'Include' END
In the second scenario, it would look like this:
- case when CustomerType = 'ENDUSER' AND Company NOT IN ('A','B','C') THEN 'Exclude'
- ELSE 'Include' END
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.6 - case when CustomerType = 'INTERCOMPANY' AND Company <> ='X' THEN 'Exclude'
-
Thank you. But this might be confusing for other people using DOMO occasionally to check on the data.
I came up with the following formula & its working but I can't include multiple company names for the 2nd instance i asked.
(
Customer Type
!= 'END') OR (Customer Type
= 'END' ANDBill-To Customer Name
= 'A') AND
(Customer Type
!= 'END') OR (Customer Type
= 'END' ANDBill-To Customer Name = 'B'
) AND
(Customer Type
!= 'END') OR (Customer Type
= 'END' ANDBill-To Customer Name
= 'C')Here in the output the logic only applies to 'Bill to Customer' - 'C' and I can see 'C' when I filter 'END' in Customer Type Column.
'A' & 'B' gets filtered out along with other 'END' companies.
Can you suggest what can be wrong with the formula?
0 -
I think what you want is this:
- (
Customer Type
!= 'END') OR (Customer Type
= 'END' ANDBill-To Customer Name IN (
'A','B','C')
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 - (
-
Yes this worked! Thank you so much! 😀 I also liked your idea of Include Exclude.
For that I'll have to create a new column and the value will be needed to be INCLUDE & EXCLUDE based on these formulas, correct?
Can I add multiple instances in 1 formula tile or each instance will need a different tile?
0 -
You can create multiple formulas with different field names in the formula tile. You are not limited to 1.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Thank you so much!😁
0 -
@SaloniShah happy to help. If you can mark as accepted any of the above answers that helped you, that will help others in the community.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
@MarkSnodgrass I tried your Include Exclude Way but when I add both instances in one Formula tile then it doesn't change anything. (picture1)
If I separate both the instances in two different tiles, only the second instance(END user instance) gets implemented and the 1st tile formula gets negated. (picture2)
Can you help with this?
0 -
The likely reason that you are running into issues with the two tiles is that you are likely using the same column name in both tiles. This results in the 2nd tile overriding what was in the first tile. I would suggest that in the IC Exception formula tile, you label the field as IC Exceptions. In the END Exception formula tile, label that field as End Exception. Then in your Filter tile, set your filter to where IC Exception equals Include or End Exception equals Include.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Got it. But why it doesn't do anything when I try to do both instances in the same tile(picture1)? Do you think there's any mistake in the 1st picture?
I can validate the formula but output doesn't change at all after running it.
0 -
In picture 1, I think you need to make it a nested case statement. The way case statements work, they exit once they find a condition that is true. A nested case statement would look like this:
- CASE WHEN condition1 THEN
- CASE WHEN condition2 THEN 'Include'
- ELSE 'Exclude'
- END
- ELSE 'Exclude'
- END
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 - CASE WHEN condition1 THEN
-
Alright, thank you so much for all your time! 😀
0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 305 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 111 SQL DataFlows
- 649 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 779 Beast Mode
- 75 App Studio
- 43 Variables
- 735 Automate
- 186 Apps
- 471 APIs & Domo Developer
- 64 Workflows
- 14 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 405 Distribute
- 117 Domo Everywhere
- 278 Scheduled Reports
- 10 Software Integrations
- 138 Manage
- 135 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive