topic Re: Sum of Values with Distinct not on Sum in App Development

Sum of Values with Distinct not on Sum

QlikQlaker — Thu, 12 May 2022 19:17:08 GMT

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:

NOT THIS (sum of all):

East	19
West	14
North	6

Please help ❤️

Re: Sum of Values with Distinct not on Sum

edwin — Thu, 12 May 2022 20:26:08 GMT

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

Re: Sum of Values with Distinct not on Sum

edwin — Thu, 12 May 2022 20:27:33 GMT

there has to be a field that can identify your personnel

Re: Sum of Values with Distinct not on Sum

vchuprina — Thu, 12 May 2022 20:54:33 GMT

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:

Regards,

Vitalii

Re: Sum of Values with Distinct not on Sum

QlikQlaker — Fri, 13 May 2022 14:56:05 GMT

Another friend was able to help me out:

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