4 Replies Latest reply: Jan 11, 2011 3:17 PM by Suchith Achyuthan RSS

    count funtion

      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.

       

       

       

       

       

        • count funtion
          Neil Miller

          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.

            • count funtion

              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.

               

               

            • AW:count funtion

               

              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.



              • count funtion

                 

                 

                 

                 



                I try and works

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