Announcement

Collapse
No announcement yet.

Count Of Unique Values In Pivot Table

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Count Of Unique Values In Pivot Table



    I'm trying to create a pivot table that will count how many employees have completed a Learning Plan. This task becomes complex (for me) because each learning plan has multiple Courses, each Course has a status of "Completed" or "Incomplete". A Learning Plan would only be considered "Completed" if all the courses within that Learning Plan were completed. In doing some research, it looks like I'll need to create another column of data, that shows per employee, per Learning Plan, if the entire Learning Plan has been completed, but I'm not sure of the best way to go about this. Please find SampleData attached.

    Thanks in Advance,

    Margs
    Last edited by Margs; June 19th, 2008, 06:12.

  • #2
    Re: Count Of Unique Values For Pivot Table

    Take a look at the Pivot Table and see if this is what you wanted.
    Attached Files
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

    Comment


    • #3
      Re: Count Of Unique Values For Pivot Table

      Thanks, but that's not exactly what I'm looking for. Ultimately I would like to show how many employees have completed a given Learning Plan. So the Pivot Table should end up displaying the names of all the Learning Plans and the count all of the employees that have completed a Learning Plan. In order for a Learning Plan to be considered to "Completed", all of the courses corresponding to that Learning Plan must have a status of "Completed."

      In order to do this, I think I'll need to create another column in my source data called Learning Plan Completion and the results should show "1" in just one of the rows per employee, per learning plan, only if all of the courses per a given learning plan, per a given employee show as "Completed" in the Course Status column. If any the courses per employee per learning plan show as Incomplete in the Course Status column, then the Learning Plan Completion column should show all "0's." This seems like data setup I'll need in order to build the pivot table I want, but I just don't know/understand the formulas needed to build the Learning Plan Completion column.

      Thanks!

      Margs

      Comment


      • #4
        Re: Count Of Unique Values For Pivot Table

        OK. Right-click on the Pivot Table and choose Wizard from the popup.
        Next, choose "Layout . . ."
        Once the layout dialog is open, in the Row field, click and drag "Employee" off of the layout.
        Next, from the list of available fields on the right side of the layout, drag "Course" onto the Row field (replacing Employee)

        Click OK and you should have what you want. You may need to drop down the page field and choose All to show all Learning Plans in the table.
        AAE
        ----------------------------------------------------

        Forum Rules | Message to Cross Posters | How to use Tags

        Comment


        • #5
          Re: Count Of Unique Values For Pivot Table

          AAE,

          Thanks again, but now the table is just the number of employees per course that have completed or have not completed the course (see attached).

          I won't be able to summarize the data I need in a pivot table until I create it. That is, I have to create a column that shows if an entire Learning Plan has been completed per employee (which requires all course per learning plan to have Course Status of "Completed").

          Thanks,

          Margs
          Last edited by Margs; June 19th, 2008, 06:12.

          Comment


          • #6


            Re: Count Of Unique Values For Pivot Table

            You could use Advanced Filter with criteria and filter unique values, with the criteria set to "=incomplete". Though multiple modules in a Learning Plan may be incomplete, the AF will provide only unique instances of the employee and Learning Plan incomplete.

            Use the filtered data as the source for the PT. Use the Learning Plan as the page field and get a count on the status.

            Optionally, you could set up your tracking differently by using three fields: one field for total modules in a Learning Plan, a field for total modules completed and the third for a true/false to evaluate the previous two fields.
            AAE
            ----------------------------------------------------

            Forum Rules | Message to Cross Posters | How to use Tags

            Comment

            Working...
            X