Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

dedwards0
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

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

Capture.PNG

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

LOAD

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