Please help me write this filter formula:

SaloniShah

• 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

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

I think what you want is this:

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

MarkSnodgrass

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

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

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

I think what you want is this:

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

SaloniShah

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

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

SaloniShah

Thank you so much!😁

MarkSnodgrass

@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

@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

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

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

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

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