avoid sum that count several time the value

[reivax31]

Hi Qlikers,

I have to tables. in the first one there each line has a unique ID

ID	Sales
A	10
B	20
C	10
D	30

In the second one for each ID I can have several attributes

ID	Attribute
A	LOW
A	MID
B	LOW
B	MID
B	HIGH
C	MID
C	HIGH
D	MID
D	MID
D	HIGH

if I use this formula: count(distinct(if(Attribute='MID',ID))) result is 4

but if I use formula sum(distinct(if(Attribute='MID',Sales))) result is 60 (but I would like to find 70)

and if I use formula sum(if(Attribute='MID',Sales)) result is 100 (because I have two lines with MID for ID=D)

what would be the syntax in order to only sum once each line in my first table ?

Thanks for your help

[isingh30]

Like this -

[reivax31 · Accepted solution]

Hi, I found the solution using

sum(aggr(if(Attribute='MID',Sales),ID))