Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
I don't think you need the TERM={'*'} unless you have null values or something because TERM={'*'} is implied.COUNT({<Type={"OWN"}>} DISTINCT TERM)
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:
I don't think you need the TERM={'*'} unless you have null values or something because TERM={'*'} is implied.COUNT({<Type={"OWN"}>} DISTINCT TERM)
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.
I try and works
count({<Type={"OWN"}>} Distinct Client)
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.