count funtion

[#1]

hello,

i have table like this

AGENCY [CLIENT NAME] TYPE TERM PROD

TEST1 ABC OWN CA AA

TEST1 DEF SUB CR AA

TEST1 abc OWN CR BB

TEST1 GHI SUB CA AA

I want to get the no of CLIENT for a particuler TYPE based on term

formula which i put is COUNT({<Type={"OWN"},TERM={"*"}>} DISTINCT UPPER([Client Name]))

the result which i got was 1, what i want was 2, ie TYPE ='OWN' and TERM = 'CA' and 'CR' here even though the customer is same PROD is different so i have to consider it as two client

request your assistance.

[#2 · Accepted solution]

Get rid of the distinct. According to your formula, you want only distinct upper case values, ABC is the same as abc, so it counts as one. If you remove the upper and kept distinct, you may still get two, but I don't know if distinct is case sensitive.

You could also change it to:

COUNT({<Type={"OWN"}>} DISTINCT TERM)

I don't think you need the TERM={'*'} unless you have null values or something because TERM={'*'} is implied.

[#3]

I guess the following....

Your filter on Type will give you only two records:

AGENCY [CLIENT NAME] TYPE TERM PROD

TEST1 ABC OWN CA AA

TEST1 abc OWN CR BB

Your convertion by UPPER will "transform" it like this:

AGENCY [CLIENT NAME] TYPE TERM PROD

TEST1 ABC OWN CA AA

TEST1 ABC OWN CR BB

Your DISTINCT count of "Client Name" will see only one Client: ABC!

Try to count without distinct.

[#4]

I try and works

count({<Type={"OWN"}>} Distinct Client)

[#5]

thanks NMilleram using upper because the CLIENT NAME is the same but by person entering the details some times enter the details in lower case.

i totally igonored this. i was concentrating on the CLIENT NAME. it works. thanks a lot.