Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
baylor2016
Creator
Creator

Nested aggregation helps.

Hi,

I am going to summarize my data (see below) at PCP level to calculate the compliance rate which is defined as the Days Since Last Visit <120 days and the Last lab test value <9. I was able to summarize my data at PAT level to get the Days Since Last Visit and the latest test value based upon the last visit date. But I cannot figure out how to calculate compliance rate at provider level. I think it need nested aggregation, but do not know how.  The qvw file is attached. Please help.

Happy New Year

Longmatch

   

PATCONTACT_DATELABRESULTPCP
1425347.2Second
2424026.5First
2425076.5First
2427116.2First
3426965.8Second
4426646Second
5424959.5First
5425579.6First
5426569.6First
5427109.7First
6424958.6First
6425578.1First
6426568.3First
6427108.3First
7424387.5Second
7426849Second
8426056.9Second
9425646.3Second
9426616.2Second
10424318.3Second
11426415Second
12425155.6Second
13424646.2Second
13426276Second
1 Solution

Accepted Solutions
Nicole-Smith

This should do the trick:

count({<PAT={'=max(CONTACT_DATE)+120>today()'}*{'=FirstSortedValue(LABRESULT, -CONTACT_DATE)<9'}>}distinct PAT)/count(distinct PAT)

I've also attached back a working copy of your example.

View solution in original post

9 Replies
jayanttibhe
Creator III
Creator III

Need more info - I think your expressions are right. If you need dataa @ PCP level then you should roll up data ( by using Appropriate Aggr function Like Sum or Avg on the Facts  DaysSinceLastVisit and Lastresults

Your information is not sufficient what you needed. Maybe you can provide the expected result Table.

Thanks

Jayant

baylor2016
Creator
Creator
Author

From my qvw attached in my initial post, you know I was able to get PAT level compliance data, the expected results (compliance rates) by PCP should be  66.6% (2/3) for PCP First and 50% (5/10) for PCP Second. How to aggregate further using PAT level summary to generate PCP level summary is my question. Thanks. 

Nicole-Smith

This should do the trick:

count({<PAT={'=max(CONTACT_DATE)+120>today()'}*{'=FirstSortedValue(LABRESULT, -CONTACT_DATE)<9'}>}distinct PAT)/count(distinct PAT)

I've also attached back a working copy of your example.

baylor2016
Creator
Creator
Author

Nicole, You are genius!!! Happy New year!!!

The logic is so simple and so effective.

By the way, I would like to learn this kind of skills, do you have a book or a reference for me? Thanks again.

jayanttibhe
Creator III
Creator III

Ohh that's cool solution Nicole.

She has taken the 'Intersection of the Set' for the 2 conditions you have mentioned @ PAT level.

baylor2016
Creator
Creator
Author

It can also be done in this way:

=sum(aggr(distinct if(today()-max(CONTACT_DATE)<120 AND FirstSortedValue(LABRESULT, -CONTACT_DATE)<9 ,1,0),PAT)) /COUNT(DISTINCT PAT)

Nicole-Smith

With the small amount of data in the example file, the calculation time is the same for both expressions, but I would expect the expression without the if statement to calculate faster over a large set of data.

baylor2016
Creator
Creator
Author

That is true. I will use your methods. I will also test the performance using my big dataset. Thanks!

baylor2016
Creator
Creator
Author

Hi Nicole,

I am learning your methods to do set analysis. Could you please review my another post at Strange set analysis results!!!? I just realized that when I change count(distinct {$<PAT_ID={'=COMPONENT_ID=1526664'}>}PAT_ID)

to count(distinct {$<COMPONENT_ID={'=COMPONENT_ID=1526664'}>}PAT_ID), It works fine. Could you please explain the your set analysis logics?

Thanks

Longmatch