Please help me write this filter formula:

SaloniShah · April 2023

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.

MarkSnodgrass · April 2023

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

MarkSnodgrass · April 2023

I think what you want is this:

(Customer Type != 'END') OR (Customer Type = 'END' AND Bill-To Customer Name IN ('A','B','C')

MarkSnodgrass · April 2023

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

MarkSnodgrass · April 2023

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

SaloniShah · April 2023

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' AND Bill-To Customer Name= 'A') AND
(Customer Type != 'END') OR (Customer Type = 'END' AND Bill-To Customer Name = 'B') AND
(Customer Type != 'END') OR (Customer Type = 'END' AND Bill-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?

MarkSnodgrass · April 2023

I think what you want is this:

(Customer Type != 'END') OR (Customer Type = 'END' AND Bill-To Customer Name IN ('A','B','C')

SaloniShah · April 2023

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?

MarkSnodgrass · April 2023

You can create multiple formulas with different field names in the formula tile. You are not limited to 1.

SaloniShah · April 2023

Thank you so much!😁

MarkSnodgrass · April 2023

@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.

SaloniShah · April 2023

@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?

MarkSnodgrass · April 2023

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.

SaloniShah · April 2023

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.

MarkSnodgrass · April 2023

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

SaloniShah · April 2023

Alright, thank you so much for all your time! 😀

Please help me write this filter formula:

Best Answers

Answers

Categories