topic Re: Count of Distinct When Two Conditions met in QlikView

Count of Distinct When Two Conditions met

proctors — Wed, 25 Nov 2020 16:16:04 GMT

I need help with an expression under the following condition:

Give me the count of patients (MRN) who have zero Office Visits ([OFFICE VISIT]) and at least one Care Coordination [CARE COORDINATION])

Re: Count of Distinct When Two Conditions met

krishnacbe — Tue, 28 Mar 2017 19:13:13 GMT

Share some sample data and expected output

Re: Count of Distinct When Two Conditions met

sunny_talwar — Tue, 28 Mar 2017 19:14:54 GMT

May be like this

Count(DISTINCT {<[OFFICE VISIT] = {0}, [CARE COORDINATION] = {'>= 1'}>}MRN)

Re: Count of Distinct When Two Conditions met

proctors — Tue, 28 Mar 2017 20:03:52 GMT

This is close...but is there a way for it to do a count of all of the Office Visits for that patient and return the count of MRN when that Office Visit Count is 0 and the count of care coordination is >1?

This basically needs to collapse across the following:

Patient A 3/2/17 Office Visit

Patient A 3/4/17 Care Coordination

Patient B 3/1/17 Care Coordination

Patient C 2/4/17 Care Coordination

The output would be that we have MRN = 2 because only two patients have only Care coordination visits (A does not qualify because Office Visit = 1 for them).

Re: Count of Distinct When Two Conditions met

sunny_talwar — Wed, 29 Mar 2017 01:45:48 GMT

May be this:

Count(DISTINCT {<MRN = {"=Count({<[OFFICE VISIT] = {'Office Visit'}>}MRN) = 0 and Count({<[OFFICE VISIT] = {'Care Coordination'}>}MRN) >= 1"}>} MRN)

Re: Count of Distinct When Two Conditions met

proctors — Wed, 29 Mar 2017 20:39:23 GMT

Definitely it! I'll have to look into that method you used where you defined MRN, I haven't seen that before in set analysis

Re: Count of Distinct When Two Conditions met

sunny_talwar — Wed, 29 Mar 2017 20:57:38 GMT

Check this out here (Under Search String):

A Primer on Set Analysis