How does one aggregate FirstSortedValue?

Anonymous — Fri, 25 Mar 2016 16:26:25 GMT

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!

Re: How does one aggregate FirstSortedValue?

sunny_talwar — Fri, 25 Mar 2016 17:08:30 GMT

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

Re: How does one aggregate FirstSortedValue?

swuehl — Fri, 25 Mar 2016 17:09:47 GMT

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?

Gysbert_Wassenaar — Fri, 25 Mar 2016 17:18:16 GMT

Add a flag field to the table in the script:

LOAD

Department,

Person,

Max(SubGroupRank) as SubGroupRank,

1 as Flag

Resident

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.

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

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

topic Re: How does one aggregate FirstSortedValue? in QlikView

How does one aggregate FirstSortedValue?

Re: How does one aggregate FirstSortedValue?

Re: How does one aggregate FirstSortedValue?

Re: How does one aggregate FirstSortedValue?

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