more complex set analysis and $expansion

Tue, 30 Apr 2013 19:54:04 GMT

My question keeps getting more complex...

we are trying to count the number of patients who receive >500 calories in their FIRST outpatient visit where the OUTPT_CALDENS field is not null (sometimes patients come in for their first outpatient visit and do not receive any calories).

here's some sample data:

PAT_MRN_ID DATE_OUTPT OUTPT_CALDENS

1 jan 1 300

1 jan 7 500

1 jan-27 700

2 march 17 650

2 march 29 400

2 april 5 325

2 april 7 800

3 jan 10 900

3 jan 14 1000

the correct count the above situation should be 2. There are two patients (ID 2 and 3 who receive >500 calories in their FIRST outpatient visit.

Here is the best proposed answer so far from Gysbert Wassenaar:

=COUNT(DISTINCT {1<[DATE_OUTPT]={"=[DATE_OUTPT]=aggr(Min([DATE_OUTPT]),PAT_MRN_ID)" },[OUTPT_CALDENS] = {">=500"}>} PAT_MRN_ID)

This works well. EXCEPT, we now realize that some patients do not receive calories on their first outpatient visit. So, we need to add a parameter that says "Minimum outpatient visit date WHERE OUTPT_CALDENS is not null"... and then start to count all thos with >500 calories...

ANY IDEAS????

Re: more complex set analysis and $expansion

Gysbert_Wassenaar — Wed, 01 May 2013 09:19:14 GMT

Try

COUNT(DISTINCT {1<[DATE_OUTPT]={"=[DATE_OUTPT]=aggr(Min({<[OUTPT_CALDENS]={'*'}>}[DATE_OUTPT]), PAT_MRN_ID)" },[OUTPT_CALDENS] = {">=500"}>} PAT_MRN_ID)

Re: more complex set analysis and $expansion

its_anandrjs — Wed, 01 May 2013 10:37:29 GMT

Hi,

Do you share any application.

Regards,

Anand

Re: more complex set analysis and $expansion

Wed, 01 May 2013 14:13:34 GMT

you are a set analysis genius!

i really, really appreciate your help!

topic Re: more complex set analysis and $expansion in QlikView

more complex set analysis and $expansion

Re: more complex set analysis and $expansion

Re: more complex set analysis and $expansion

Re: more complex set analysis and $expansion