Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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"
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.
I think you should use Capitalize() in script to have 'Secret' in all situations and then just try -
=count({<NOTE_TO_CO={'Secret'}>} [IDs])
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.
Thank you both. I get the desired result.