topic Count Distinct in QlikView

Count Distinct

Fri, 30 Sep 2011 13:08:00 GMT

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

Count Distinct

its_anandrjs — Fri, 30 Sep 2011 13:14:20 GMT

Hi,

Use some thing

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

Rgds

Anand

Count Distinct

swuehl — Fri, 30 Sep 2011 13:14:22 GMT

Hm, not sure, but try

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

if PersonID is your person field.

Hope this helps,

Stefan

Count Distinct

SunilChauhan — Fri, 30 Sep 2011 13:16:29 GMT

try this

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

Count Distinct

SunilChauhan — Fri, 30 Sep 2011 13:18:21 GMT

or if u have Id then

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

Count Distinct

Fri, 30 Sep 2011 13:18:31 GMT

Fantastic, thank you.