3 Replies Latest reply: Mar 25, 2016 1:18 PM by Gysbert Wassenaar RSS

    How does one aggregate FirstSortedValue?

    David Edwards

      All,

       

      I'm looking to calculate by department, the total number of people who have different numeric rankings, and I'm having difficulty in figuring out a solution.

       

      Below is an example of the data:

       

      DepartmentPersonTeamSubGroupSubGroupRank
      D1P1T1SG11
      D1P1T1SG22
      D1P1T1SG33
      D1P1T2SG11
      D1P1T2SG22
      D1P2T2SG11
      D1P2T2SG22
      D2P3T3SG11
      D2P3T3SG22
      D2P3T3SG44

       

      The data is related in this way:

           The department and person has a 1:1 relationship.

           A person can be related to multiple teams.

           A team can have multiple subgroups.

           If a person is a member of a team, then they inherit all subgroups belonging to that team.

           Each subgroup has a specific ranking / importance level.

           I assigned a numeric ranking system to each distinct subgroup.

       

      Originally I had 4 tables loaded that were joined by a key field in each.  Then I decided I would load the 4 tables with joins in the script to create one large flat table, as I thought that perhaps it might make sense to flag each row as to whether or not it contains a max value and what that max value is.  I haven't figured out how to accomplish creating those two additional fields, if that is even the right way to go with this.

       

      Within my applicationI was able to create a pivot table chart that uses the following dimensions and expressions:

      DIMENSION = Department, Person

      EXPRESSION = FirstSortedValue(DISTINCT SubGroup, SubGroupRank)

       

      That resulted in a detailed breakdown by Department of each user and what their max rank was.    I can't figure out how to aggregate it into a department only view.

       

      What I am seeking to solve for is the following:

           What is the total count of max rank for each person, by department?

       

      An example of the desired output is as follows:

       

      DepartmentSubGroup Rank 1SubGroup Rank 2SubGroup Rank 3SubGroup Rank 4
      D10110
      D20001

       

      Or:

       

      DeparmentSubGroupSubGroupSubGroupSubGroup
      1234
      D10110
      D20001

       

      Either of the above provides the answers I am looking for.

       

      Here is an explanation of what the above charts show:

           Departments D1 and D2 have 0 people who have a max SubGroupRank of 1.

           Department D1 has 1 person (person P2) who has a max SubGroupRank of 2.  Department D2 has 0 people.

           Department D1 has 1 person (person P1) who has a max SubGroupRank of 3.  Department D2 has 0 people.

           Department D2 has 1 person (person P3) who has a max SubGroupRank of 4.  Department D1 has 0 people.

       

      Any help is much appreciated!  Thanks!