2 Replies Latest reply on Dec 11, 2013 10:21 AM by bill.cunningham

    Distinct Count using a partition

    Dan Gerena

      My data looks like this:

       

      Manager       Program      Work_Package

      Joe Smith          ABC               ABC1

      Joe Smith          ABC               ABC2

      Joe Smith          ABC               ABC2

      Joe Smith          ABC               ABC3

      Joe Smith          DEF               DEF1

      Joe Smith          DEF               DEF2

      Joe Smith          DEF               DEF2

      Joe Smith          MNO               MNO1

      Joe Smith          MNO               MNO2

      Jane Doe          XYZ                    XYZ1

       

      I want to do a count distinct by Manager, such that my result ignores the 2 bolded records above and my result set is:

      Manager   Count Distinct

      Joe Smith          7

      Jane Doe          1

       

      How can I accomplish this?

        • 1. Re: Distinct Count using a partition
          Joshua Milligan

          Dan,

           

          If you are trying to get a count distinct of program and work_package, then you could concatenate those fields in a calculation and then do a count distinct of that field.

           

          [Program_Work_Package]

          [Program] + [Work_Package]

           

          Place that calculation in the view and change it to a measure (COUNTD).

           

          Regards,

          Joshua

          • 2. Re: Distinct Count using a partition
            bill.cunningham

            Hey Dan,

             

            Joshua's suggestion works very well when you are not already partitioning by the manager.  However, for the example you gave, you should be able to do it without any calculated fields (as long as your data is stored as an extract) by placing Manager on the Rows shelf and then just doing a count distinct on the Work Package field.

            CropperCapture[53].jpg

             

            Hope this helps!

            Bill

            1 of 1 people found this helpful