Set expression to count not null values and match with the specified text using wild character

[pgloc2020]

Hello all,

I have a data with the following column names-

IDs, Note_To_CO, Receipt Date and some other non relevant columns

I am trying to create a bar chart and in the X-axis I have Receipt Year and Y-axis I want to calculate IDs based on the condition where Note_To_CO is not null and have containing text  "*ecret*". 

( Since in my data, "secret" word is written in both lower and upper case, which is why I used *ecret*)

I have used following expressions but none of them is giving me correct counts-

=count({<NOTE_TO_CO =- {''}, NOTE_TO_CO={'*ecret*'}>} [IDs])

//=count (distinct {<NOTE_TO_CO = {"*ecret*"}, NOTE_TO_CO = {"*"} >} [IDs])

//Count({<NOTE_TO_CO =- {''}, NOTE_TO_CO={'*ecret*'}>} IDs)

//Count({<NOTE_TO_CO =- {'=len(trim(NOTE_TO_CO))=0'}, NOTE_TO_CO={'*ecret*'}>} IDs)

//Count({<NOTE_TO_CO ={"*"},NOTE_TO_CO={'*ecret*'}>} IDs)

//=if(isnull(NOTE_TO_CO),count({$<NOTE_TO_CO={'*ecret*'}>} IDs))

//Count({$-<NOTE_TO_CO ={'*'} , NOTE_TO_CO={'*ecret*'}>} IDs)

//=count (distinct {<NOTE_TO_CO = {'*ecret*'}, NOTE_TO_CO -= {"-"} >} [IDs])

//=count (distinct {<NOTE_TO_CO = {'*ecret*'}, NOTE_TO_CO *= {"*"} >} [IDs])

Please help me how  I can achieve the correct counts in Bar chart as well as in the KPI chart where Note_To_CO is not null and the comment listed in the Note_To_CO Column contains text "secret" or "Secret"

[Digvijay_Singh]

I think you should use Capitalize() in script to have 'Secret' in all situations and then just try - 

=count({<NOTE_TO_CO={'Secret'}>} [IDs])

[hic · Accepted solution]

A couple of things:
* NULL is not selectable. So, NOTE_TO_CO =- {''} will not work.
* If you have several references to the same field in your set expression, the leftmost will be ignored
* Searches must be enclosed in double quotes. Not single quotes.
* Searches are case insensitive.
* Demanding NOTE_TO_CO ={"*secret*"} is enough to exclude NULLs

So, I suggest you try

Count({<NOTE_TO_CO ={"*secret*"}>} IDs)

It should be enough.

[pgloc2020]

Thank you both. I get the desired result.