Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikQlaker
Contributor III
Contributor III

Sum of Values with Distinct not on Sum

For background, I work with a lot of related data. In this particular case, there are 2 data loads that are joined together. The problem is that I need the SUM of values based on 1 distinct field (not the value summed) and separated by another field...

Region Store Personnel ID
       
East 2 5 56987
West 1 5 68516
North 2 6 98741
West 3 5 36549
East 2 8 85444
East 1 5 56987
East 4 1 68753
West 5 2 86548
West 4 2 86548

 

What I am looking for, is a Sum of the personnel but the ID has to be distinct (because it can be linked to more than 1 store) 

I'm trying to use this as a measure for the Regions (with some other stuff) but i can't seem to get the # of personnel.

The end result would look like:

QlikQlaker_0-1652382887921.png

NOT THIS (sum of all):

East  19
West 14
North 6

 

Please help ❤️

Labels (1)
1 Solution

Accepted Solutions
QlikQlaker
Contributor III
Contributor III
Author

Another friend was able to help me out:

 

Sum(Aggr(Only(Personnel),ID))

View solution in original post

4 Replies
edwin
Master II
Master II

if i understand it corrcetly, the column personnel stands for the number of employees and that ID is the store ID.  could the personnel entity have an identity associated with it some sort of personnelID?  if so just count distinct personnelID 

edwin
Master II
Master II

there has to be a field that can identify your personnel

vchuprina
Specialist
Specialist

Hi, 

You can find max value per Region and ID

DataTMP:
LOAD *,
     Region &'-'& ID AS Key
     ;
LOAD * Inline [
Region,   Store,    Personnel, ID              
East, 2,   5,   56987
West, 1,   5,   68516
North,    2,   6,   98741
West, 3,   5,   36549
East, 2,   8,   85444
East, 1,   5,   56987
East, 4,   1,   68753
West, 5,   2,   86548
West, 4,   2,   86548
];

Data:
LOAD
     Key,
     Max(Personnel) as PersonnelNew
Resident DataTMP
Group By Key;

Result:

vchuprina_0-1652389016114.png

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
QlikQlaker
Contributor III
Contributor III
Author

Another friend was able to help me out:

 

Sum(Aggr(Only(Personnel),ID))