topic Re: Nested aggregation helps. in QlikView

Nested aggregation helps.

baylor2016 — Tue, 27 Dec 2016 21:13:37 GMT

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

Re: Nested aggregation helps.

jayanttibhe — Tue, 27 Dec 2016 21:56:32 GMT

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

Re: Nested aggregation helps.

baylor2016 — Tue, 27 Dec 2016 22:07:20 GMT

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.

Re: Nested aggregation helps.

Nicole-Smith — Tue, 27 Dec 2016 22:46:03 GMT

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.

Re: Nested aggregation helps.

baylor2016 — Tue, 27 Dec 2016 22:52:15 GMT

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.

Re: Nested aggregation helps.

jayanttibhe — Wed, 28 Dec 2016 03:13:44 GMT

Ohh that's cool solution Nicole.

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

Re: Nested aggregation helps.

baylor2016 — Wed, 28 Dec 2016 16:29:57 GMT

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)

Re: Nested aggregation helps.

Nicole-Smith — Wed, 28 Dec 2016 17:03:59 GMT

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.

Re: Nested aggregation helps.

baylor2016 — Wed, 28 Dec 2016 21:29:11 GMT

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

Re: Nested aggregation helps.

baylor2016 — Fri, 06 Jan 2017 02:12:32 GMT

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