topic Re: Help with Chart Expression - Exclude records in App Development

Help with Chart Expression - Exclude records

JFDemers — Fri, 17 Feb 2023 14:26:28 GMT

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!

Re: Help with Chart Expression - Exclude records

henrikalmen — Fri, 17 Feb 2023 14:59:31 GMT

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

Re: Help with Chart Expression - Exclude records

BrunPierre — Fri, 17 Feb 2023 16:00:58 GMT

May be like this in set analysis

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

Re: Help with Chart Expression - Exclude records

JFDemers — Fri, 17 Feb 2023 15:05:54 GMT

Works like a charm! Thx a lot!

Re: Help with Chart Expression - Exclude records

henrikalmen — Fri, 17 Feb 2023 15:16:14 GMT

Great!

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

Re: Help with Chart Expression - Exclude records

JFDemers — Fri, 17 Feb 2023 15:16:20 GMT

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)

Re: Help with Chart Expression - Exclude records

henrikalmen — Fri, 17 Feb 2023 15:19:25 GMT

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

Re: Help with Chart Expression - Exclude records

JFDemers — Fri, 17 Feb 2023 15:22:13 GMT

Thx again!

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

Re: Help with Chart Expression - Exclude records

BrunPierre — Fri, 17 Feb 2023 16:06:25 GMT

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