Nested aggregation helps.

[baylor2016]

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

PAT	CONTACT_DATE	LABRESULT	PCP
1	42534	7.2	Second
2	42402	6.5	First
2	42507	6.5	First
2	42711	6.2	First
3	42696	5.8	Second
4	42664	6	Second
5	42495	9.5	First
5	42557	9.6	First
5	42656	9.6	First
5	42710	9.7	First
6	42495	8.6	First
6	42557	8.1	First
6	42656	8.3	First
6	42710	8.3	First
7	42438	7.5	Second
7	42684	9	Second
8	42605	6.9	Second
9	42564	6.3	Second
9	42661	6.2	Second
10	42431	8.3	Second
11	42641	5	Second
12	42515	5.6	Second
13	42464	6.2	Second
13	42627	6	Second

[jayanttibhe]

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]

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 · Accepted solution]

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]

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]

Ohh that's cool solution Nicole.

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

[baylor2016]

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]

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

[baylor2016]

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