# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor II

## How does one aggregate FirstSortedValue?

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!

3 Replies
MVP

## Re: How does one aggregate FirstSortedValue?

May be this:

Sum(If(Aggr(Max(TOTAL <Person> Aggr(FirstSortedValue(DISTINCT SubGroupRank, -SubGroupRank), Department, Person)), Department, Person, SubGroupRank) = Aggr(FirstSortedValue(DISTINCT SubGroupRank, -SubGroupRank), Department, Person, SubGroupRank), 1))

MVP

## Re: How does one aggregate FirstSortedValue?

Maybe something like this:

Create two dimensions (2nd is a calculated dimension):

1)     Department

2)     =Aggr( Max(SubGroupRank), Department, Person)

and one expression:

=Count(DISTINCT Person)

## Re: How does one aggregate FirstSortedValue?

Add a flag field to the table in the script:

Department,

Person,

Max(SubGroupRank) as SubGroupRank,

1 as Flag

Resident

T1

Group By

Department,

Person

;

Then create a pivot table and use Department and SubGroup as dimensions and =Count({<Flag={1}>}distinct Person)+sum({1}0) as expression and disable the Suppress Zero-values option of the pivot table.

talk is cheap, supply exceeds demand