avoid sum that count several time the value

reivax31 — Sat, 26 May 2018 13:48:16 GMT

Hi Qlikers,

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

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

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

Re: avoid sum that count several time the value

isingh30 — Sat, 26 May 2018 16:26:43 GMT

Like this -

reivax31 — Sat, 26 May 2018 19:51:06 GMT

Hi, I found the solution using

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