10 Replies Latest reply: Mar 1, 2017 12:26 AM by Jia Sheng Loh RSS

    Distinct?

    Jia Sheng Loh

       

       

      HI all expert!

       

      I have this set of data,

       

         

      Contact PersonContact NumberCOMPANY
      MA11111111ABC
      MA11111111DEF
      MA22222222GHI
      MB22222222JIL
      MC33333333MNO
      MD33333333PQR
      ME33333333STU
      ME33333333

      VWX

       

       

      ME3333333YZA

       

       

       

      I would like to find out the number of cases where same contact person, multiple company. So in this set of data, it will be 2 (MA & ME both represent multiple company).

       

      I would not like to touch my scripting as much as possible. How can I expression in my KPI to show the number of case.

       

       

      Regards,

      Jia

        • Re: Distinct?
          Jayaseelan K

          Hi,

               Please post you expecting output In .xsl or something else.

           

              This is your output?

          Capture.PNG

           

          Thanks,

            • Re: Distinct?
              Jia Sheng Loh

              Hello Jayaseelan,

               

              Thank for ur prompt help, even for my others qns as well.

               

              My expected output for kpi will be

               

              CapturePersonal.PNG

              and my expected output in result table will be

               

                 

              Contact PersonContact NumberCOMPANY
              MA11111111ABC
              MA11111111DEF
              MA22222222GHI
              ME33333333STU
              ME33333333VWX
              ME33333333YZA
                • Re: Distinct?
                  Celambarasan Adhimulam

                  Hi,

                   

                  KPI should be Count({<[Contact Person] = {"=Count(Distinct [Contact Numer] &'_' & COMPANY)>1"}>} DISTINCT [Contact Person])

                   

                  Table

                  Dimension - [Contact Person], [Contact Number]

                  Expression - Only({<[Contact Person] = {"=Count(Distinct [Contact Numer] &'_' & COMPANY)>1"}>} COMPANY)

                    • Re: Distinct?
                      Jia Sheng Loh

                      Hi Celambarasan,

                       

                      could you explain what  {"=Count(Distinct [Contact Number] &'_' & COMPANY)>1"} does?

                       

                      and also if I want to change to  same CONTACT NUMBER, multiple company, how do I go around it?

                       

                       

                      Regards,

                      Jia

                        • Re: Distinct?
                          omar bensalem

                          Hi Jia,

                           

                          I explained already explained the process:

                           

                          Now to count the numeros existing in different companys, here how you proceed:

                           

                          Count({<numero = {"=Count(Distinct numero&company)>1"}>} DISTINCT numero)

                            • Re: Distinct?
                              Jia Sheng Loh

                              Hi Omar,

                               

                              Thank for the clarification!

                               

                              Yes yes, guess my post was slightly later, thus missing out your explanation before posting.

                               

                               

                              So the table wise, correct me if I am wrong. instead of count, we want to show the only the result, thus

                               

                              Only({<[Contact Person] = {"=Count(Distinct [Contact Person] &'_' & COMPANY)>1"}>} COMPANY)?

                               

                               

                    • Re: Distinct?
                      omar bensalem

                      It's :
                      Count({<contact = {"=Count(Distinct contact&company)>1"}>} DISTINCT contact)

                       

                      we want to count distinct contact;

                      So the original expression is count(distinct contact)

                       

                      Now, we don't want to count all of them but only the contact who has more then a company;

                      To do so; we create a 'new field' contact&company and count it distinct:

                       

                      exp: for MD we'll only have MD&PQR : so 1
                      for ME: we'll have : ME&STE + ME/ VWX +ME&YZA : so 3 > 1 , we count it and so on;

                       

                       

                       

                      which leads us to 2 contacts;

                      hope this helps !

                      • Re: Distinct?
                        Jayaseelan K

                        Hi Jia Sheng Loh,

                         

                          Use the following expression you will get you answer.

                         

                        Count({<[Contact Person] = {"=Count(Distinct COMPANY)>1"}>} DISTINCT [Contact Person])

                         

                        Capture1.PNG

                         

                        In the above expression we counting the Distinct Company and Checking with the [Contact person] is > 1 and Count the over all Distinct [Contact person].

                         

                        Thanks,

                          • Re: Distinct?
                            Jia Sheng Loh

                            Hi Jayaseelan,

                             

                            Seems like the code Celambarasan, Omar and you provided works,

                             

                            But since Omar came up with Explanation first, I think is fair to choose his as correct answer so that other that tumble upon this question will understand as well.

                             

                            Ur explanation is correct and helpful too!

                             

                            Thank you!

                             

                            Regards,

                            Jia