Help with Chart Expression - Exclude records

[JFDemers]

Hi,

I am trying to exclude records for which their AccountCode starts with a specific string (5*,6*,40* etc.). I tried multiple ways but could not make it work.

I tried:

=IF((AccountClientType='Institutional' OR AccountClientSubType='Private Wealth')
AND AccountCode <> '5*',
AccountDisplayCodeName)

=IF((AccountClientType='Institutional' OR AccountClientSubType='Private Wealth')
AND NOT MATCH(AccountCode,'5*','6*'),
AccountDisplayCodeName)

Thx for the help!

[henrikalmen · Accepted solution]

use WILDMATCH instead of MATCH in your second example. Match doesn't accept wildcards (*) but wildmatch does.

[BrunPierre]

Hi

May be like this in set analysis

{<AccountCode -= {"5*", "6*","40*", .... and so on}>}

[JFDemers]

Works like a charm! Thx a lot!

[henrikalmen]

Great!

It's always a good idea to click the "accept as solution" button when a question has recieved a correct answer.

[JFDemers]

I actually need set analysis for another expression and this one is not working either. I am trying to count the number of records with these parameters:

=COUNT({<AccountClientType={'Institutional'},AccountCode -= {'9*'}>} DISTINCT AccountCode) +
COUNT({<AccountClientSubType={'Private Wealth'},AccountCode -= {'5*', '6*','40*','48*','49*'}>} DISTINCT AccountCode)

[henrikalmen · Accepted solution]

When using wildcards in set expression, you need to enclose the values with double quotes instead of single quotes: "5*" and not '5*'.

[JFDemers]

Thx again!

Still learning the syntax. Might have missed that detail in the documentation.

[BrunPierre · Accepted solution]

=COUNT({<AccountClientType={'Institutional'},AccountCode -= {"9*"}>} DISTINCT AccountCode) +
COUNT({<AccountClientSubType={'Private Wealth'},AccountCode -= {"5*", "6*","40*","48*","49*"}>} DISTINCT AccountCode)