# How does one aggregate FirstSortedValue?

**David Edwards**Mar 25, 2016 12:26 PM

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:

Department | Person | Team | SubGroup | SubGroupRank |
---|---|---|---|---|

D1 | P1 | T1 | SG1 | 1 |

D1 | P1 | T1 | SG2 | 2 |

D1 | P1 | T1 | SG3 | 3 |

D1 | P1 | T2 | SG1 | 1 |

D1 | P1 | T2 | SG2 | 2 |

D1 | P2 | T2 | SG1 | 1 |

D1 | P2 | T2 | SG2 | 2 |

D2 | P3 | T3 | SG1 | 1 |

D2 | P3 | T3 | SG2 | 2 |

D2 | P3 | T3 | SG4 | 4 |

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 |
---|---|---|---|---|

D1 | 0 | 1 | 1 | 0 |

D2 | 0 | 0 | 0 | 1 |

Or:

Deparment | SubGroup | SubGroup | SubGroup | SubGroup |
---|---|---|---|---|

1 | 2 | 3 | 4 | |

D1 | 0 | 1 | 1 | 0 |

D2 | 0 | 0 | 0 | 1 |

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!