1 2 Previous Next 17 Replies Latest reply on Sep 12, 2019 8:30 AM by Ashfaque Shaikh

# Duplicate values

Hi, I have below data and would like to see if I can avoid duplicate  while calculating.  here in below table when I am trying to use IIF([Purchased]="Y",1,0) then I am getting for ABC  count  as 2, but I wanted to display only once since it has Y.  Please help me fixing this.

CompanyPurchased
ABCY
DEFN
ABCY
GEFY
STRY
• ###### 1. Re: Duplicate values

Hi Abdul

Try the calculation

{ Fixed [Company]: SUM(IIF([Purchased]="Y",1,0))}

• ###### 2. Re: Duplicate values

Hi, Abdul

Try this

{ Fixed [Company]: AVG(IIF([Purchased]="Y",1,0))}

ZZ

• ###### 3. Re: Duplicate values

Hi Abdul,

Check with below formula

SUM(IF {FIXED [Company] : COUNTD([Purchased])=1} then 1 else 0 end)

Thanks,

Ashfaque

• ###### 4. Re: Duplicate values

I think you're after SUM not COUNT ..... COUNT will be 2 (as in 1 and 0) but SUM will be 1 ( 1 + 0 )

1 of 1 people found this helpful
• ###### 5. Re: Duplicate values

It is not working for me

• ###### 6. Re: Duplicate values

It throws error Integer cant compare with String

• ###### 7. Re: Duplicate values

yes, I am looking for count not sum

• ###### 8. Re: Duplicate values

you [Purchased] is a integer?

ZZ

• ###### 9. Re: Duplicate values

no, but AVG(IIF([Purchased]="Y" those error, because it is looking for integer

• ###### 10. Re: Duplicate values

so it is something like this with your above sample data?

ZZ

• ###### 11. Re: Duplicate values

Checked  it worked for me

SUM(IF {FIXED [Company] : COUNTD([Purchased])=1} then 1 else 0 end)

• ###### 12. Re: Duplicate values

Given the example data above, there is no need to use an LOD here.  Wouldn't this work the same way ?

SUM(IF COUNTD([Purchased])=1 then 1 else 0 end)

1 of 1 people found this helpful
• ###### 13. Re: Duplicate values

Hi Abdul,