Count Distinct

[#1]

I'm looking to count distinct ID's where postcode is null and consent is yes.  I can get the formula to work before adding the distinct part but this is giving me the same person on more than one occasion, hence the need to make it distinct. So the formula I have is - sum(if(isnull(NDTMSdat) and NDTMSconsent='Y',1)) how do I get this to one count per person?

Thanks

[its_anandrjs]

Hi,

Use some thing

sum(if(isnull(NDTMSdat) = -1 and NDTMSconsent='Y',1))

Rgds

Anand

[swuehl · Accepted solution]

Hm, not sure, but try

=count(DISTINCT if(isnull(NDTMSdat) and NDTMSconsent='Y', PersonID))

if PersonID is your person field.

Hope this helps,

Stefan

[SunilChauhan]

try this

sum(distinct if(isnull(NDTMSdat) and NDTMSconsent='Y',1))

[SunilChauhan]

or if u have Id then

sum(distinct if(isnull(NDTMSdat) and NDTMSconsent='Y',IDfield))

[#6]

Fantastic, thank you.