Finding Count of Field against another field distinct values(Ignoring duplicates)

[sharathkolluru1803]

Hi Experts,

I want your help in finding the distinct number(count) of IDs where table is having 2 coloumns ID and Order,
1)Order values considered for distinct ID if those are different.
2)If the ID and Order combination is single value needs to be ignored
3)If the ID and Order combination repeats then those needs to be ignored.

ID                  Order
ghi123         449
ghi123         393
lmn123       543
xyz123         999
xyz123         999
xyz123         999
pqr123        678
pqr123        445
pqr123        877
abc123       436
abc123       436

After applying above conditions ID count should be 2 (ghi123, pqr123) as the values are differed.

I tried with Chart expression "Sum(Aggr(Count(distinct Order)>1,ID))" like this but result are not correct.

Please do help me to solve this issue? Thanks a ton in advance.

[sunny_talwar · Accepted solution]

Your expression is acting like an if statement within the Sum(Aggr())

Count(distinct Order) > 1 gives -1 when true and 0 when false per id.... and then you add them up.... The better way to code this is

Sum(Aggr(If(Count(DISTINCT Order) > 1, 1, 0), ID))