Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
koscumbs
Contributor III
Contributor III

Aggregation within aggregation within aggregation.

Good afternoon

 

I'm at my wit's end trying to do some admittedly complex stuff (although to the whiz's on this site, I'm sure it's trivial).  The general concept is that I have two raw data tables in my QVW, one has patient visit volume (table1), the other has orders (table2) for those same patients.  I want Qlik on the fly to tell me which providers order more labs from table2 per patient than the mean (ideally, I'd love to be able to sub that out for 40th percentile by provider, 30th percentile, etc).  So, which providers have an count(orders)/count(patients) which is greater than the mean.

I've come up with that piece in pivot/straight tables when I am aggregating by provider...no problem:

=SUM(aggr(((count({<ORDER_TYPE_NAME = {'Lab'}>}ORDER_TYPE_NAME)
/count(distinct PAT_ENC_CSN_ID) - count(total {<ORDER_TYPE_NAME = {'Lab'}>}ORDER_TYPE_NAME)
/count(distinct total PAT_ENC_CSN_ID))/count(total {<ORDER_TYPE_NAME = {'Lab'}>}ORDER_TYPE_NAME)
/count(distinct total PAT_ENC_CSN_ID))
*
if(count({<ORDER_TYPE_NAME = {'Lab'}>}ORDER_TYPE_NAME)
/count(distinct PAT_ENC_CSN_ID)>avg(total aggr(count({<ORDER_TYPE_NAME = {'Lab'}>}ORDER_TYPE_NAME)
/count(distinct PAT_ENC_CSN_ID),PROV_ID)),sum({<ORDER_TYPE_NAME ={'Lab'}>} CHARGE_AMT),0),PROV_NAME))

The problem is when I want to use a different dimension (for example, patient diagnosis), I'm having a hell of a time.

The way I'm thinking of it, is to create for whatever my dimension is, a count of patients who were seen by providers who were above/below the 50th percentile of ordering per patient. 

Any help is appreciated. 

Labels (4)
1 Reply
Brett_Bleess
Former Employee
Former Employee

Likely going to need to attach QVW example with data model and some details on your expected outcomes, screenshots etc. in order to get some responses.  If you need to scramble any sensitive fields, you can use Settings\Document Properties\Scrambling tab too.

Only thing I have that might get your somewhere is the following potentially:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.