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

  • MarkSnodgrass
    Answer ✓

    I think what you want is this:

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MarkSnodgrass
    Answer ✓

    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 to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • 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
    Answer ✓

    I think what you want is this:

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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?

  • 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 to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Thank you so much!😁

  • @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 to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • @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?

  • 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 to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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
    Answer ✓

    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 to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Alright, thank you so much for all your time! 😀