Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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
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.
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.
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.
Ohh that's cool solution Nicole.
She has taken the 'Intersection of the Set' for the 2 conditions you have mentioned @ PAT level.
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)
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.
That is true. I will use your methods. I will also test the performance using my big dataset. Thanks!
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