2 Replies Latest reply on Feb 4, 2019 4:34 PM by Zhouyi Zhang

    Calculating Number of Records Based on Fields

    mohini.yende

      Hi Everyone,

       

      I'm currently working on a dataset to analyze training completion records of users. A training/course can be a part of one or multiple curriculums. But I don't want to consider duplicate records generated because of the curriculum field. It is possible that the same training was assigned multiple times and I want to count those assignments. Following are the sample records from the dataset

       

      Employee Name
      Department
      Training Assigned DateTraining Completion DateTraining StatusTraining Title
      Curriculum
      John DoeCustomer Care11/20/201812/15/2018CompletedEffective CommunicationCommunications
      John DoeCustomer Care11/20/201812/15/2018CompletedEffective CommunicationAdvance Communications
      John DoeCustomer Care11/20/201812/15/2018CompletedEffective CommunicationCommunications for Services Department
      John DoeCustomer Care2/1/2019NullIn ProgressEffective CommunicationAdvance Communications
      Mark RobinCustomer Care11/20/201812/23/2018CompletedEmail WritingCommunications
      Mark RobinCustomer Care11/20/201812/23/2018CompletedEmail WritingAdvance Communications
      Mark RobinCustomer Care11/20/201812/28/2018CompletedEffective CommunicationCommunications
      Mark RobinCustomer Care11/20/201812/28/2018CompletedEffective CommunicationAdvance Communications
      Mark RobinCustomer Care11/20/201812/28/2018CompletedEffective CommunicationCommunications for Services Department

       

      In the above table, Joh Doe completed "Effective Communication" training once. But, because the training is a part of 3 curriculums we get here 3 completion records for the same training. Calculating total trainings on John Doe's transcript should be = 2 and trainings on Mark's transcript should be = 2. And, total assignments in "Customer Care" department should be 4. When I omit the Curriculum field from dataset and select only unique records based on the other 6 fields. Then I get the desired result. However, if I keep the Curriculum field then I get inflated numbers. e.g. total trainings on John Doe's transcript = 4 and trainings on Mark's transcript = 5. I want to design dashboard in such way that top worksheet shows total trainings which do not consider Curriculum and bottom view should give tabular details of each training which will look like the table mentioned above and should display all the curriculums. Please advise how to proceed with this issue.

       

      Thanks in advance.