topic Re: complex set analysis with $ expansions in QlikView

complex set analysis with $ expansions

Tue, 30 Apr 2013 18:04:51 GMT

Hello,

we are trying to count the number of patients who receive >500 calories in their FIRST outpatient visit. Please see below for details... would appreciate any additions to Sushil's propsed solution.

Sushil posed the following solution:

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

Thanks to Sushil for taking a stab at it...

so it doesn't seem to work.

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.

thank you SO much.

Re: complex set analysis with $ expansions

Gysbert_Wassenaar — Tue, 30 Apr 2013 18:22:41 GMT

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

Re: complex set analysis with $ expansions

Tue, 30 Apr 2013 18:27:16 GMT

=count(distinct{1<[DATE_OUTPT]={"$(=(date(min([DATE_OUTPT]))))"},[OUTPT_CALDENS]={">=500"}>}PAT_MRN_ID). You need to format min([Date_outpt]) so that it is the same of the format of date_outpt; if you do not it will take an integer value instead and no matches will be returned. If date() does not work consider MonthName().

Re: complex set analysis with $ expansions

Tue, 30 Apr 2013 19:35:16 GMT

you are awesome!

can you explain to me a little bit of the theory behind what you did... I see you got rid of the $ exression but don't quite understand why the below works...

1<[DATE_OUTPT]={"=[DATE_OUTPT]=aggr(Min([DATE_OUTPT]),PAT_MRN_ID)" }

THANK YOU!

Re: complex set analysis with $ expansions

Tue, 30 Apr 2013 19:45:40 GMT

also, I'll just add one other question:

it seems that this set picks up minimum dates where there is no Cal Dens associated with the date. I should have mentioned this before: there can be outpatient clinic dates where the patient receives no calories. So, we really want the minimum date, where cal dens is not blank-- and then count the ones greater than 500 from there.

sorry, forgot about that....