topic Re: Using set analysis + aggregation in QlikView

Using set analysis + aggregation

koscumbs — Tue, 09 Jan 2018 19:35:03 GMT

Hi all,

I'm trying to perform a function on a column, but only where certain conditions are met.

The example is a healthcare dataset -- I have ~100,000 unique patients, and I have ~700,000 unique encounters with those patients.

I've created a variable called "VISIT_ORDER," which is simply a ranking of the patients visit sequence. I also have a variable which specifies the patient's dialysis status on the encounter level: whether the patient is not currently on dialysis, if they currently are on dialysis, or if they've never been on dialysis during the time window.

I'm trying to average a column with the first visit a patient has (my thought was minimizing VISIT_ORDER) after they've been on dialysis. In my set analysis, I want to return the result of the row where VISIT_ORDER = the minimum VISIT_ORDER where DIALYSIS_DX_GROUPING = 'AFTER DIALYSIS', then to average COUNT_ADMITS_365 for those rows.

I think what I need to do is some variant of this below, but while what I have works for one patient (when I've been troubleshooting), I'm having issues:

=avg(

{<VISIT_ORDER = {"$(=aggr(min({<DIALYIS_DX_GROUPING = {'AFTER DIALYSIS'}>}VISIT_ORDER),PAT_ID))"}>}

COUNT_ADMITS_365)

The issue I'm having I believe is with the dollar-sign expansion, and also why the function appears to work with a single patient (PAT_ID is the unique patient identifier).

Any help would be appreciated; driving myself nuts over here.

Thanks!

Steve

Re: Using set analysis + aggregation

Anil_Babu_Samineni — Wed, 10 Jan 2018 01:40:12 GMT

An advanced search is creating an hyper cube with VISIT_ORDER as dimension and

=avg({<VISIT_ORDER = {"=VISIT_ORDER = aggr(min({<DIALYIS_DX_GROUPING = {'AFTER DIALYSIS'}>}VISIT_ORDER),PAT_ID)"}>} COUNT_ADMITS_365)

Re: Using set analysis + aggregation

justinphamvn — Wed, 10 Jan 2018 02:46:49 GMT

Hi Stephen Koscumb,

Can you share with me samples data and result you want?

Thank you!

Justin

Re: Using set analysis + aggregation

sunny_talwar — Wed, 10 Jan 2018 09:30:51 GMT

May be try this

Sum(Aggr(If(VISIT_ORDER = Min({<DIALYIS_DX_GROUPING = {'AFTER DIALYSIS'}>} TOTAL <PAT_ID> VISIT_ORDER), 1, 0), PAT_ID, VISIT_ORDER))

Re: Using set analysis + aggregation

koscumbs — Wed, 10 Jan 2018 15:14:40 GMT

This one was the closest to the answer -- for the record, this is what I needed:

=SUM(

Aggr(

If(VISIT_ORDER = Min({<DIALYIS_DX_GROUPING = {'AFTER DIALYSIS'}>} TOTAL <PAT_ID> VISIT_ORDER), COUNT_ADMITS_365, 0)

, PAT_ID, VISIT_ORDER))

Thanks to everyone!