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:
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:
|Department||SubGroup Rank 1||SubGroup Rank 2||SubGroup Rank 3||SubGroup Rank 4|
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!