Set Analysis Combining with AGGR function

[brentski]

Good Day to all!  Hoping to get some help on combining individual sets that work into a single set.

Business Question: How many people who have seen a PCP also have an ED Visit per year 2018, 2019, 2020.

Set 1 People with PCP (Primary Care Provider) visit: 

sum(if(aggr(sum({$<[Service Category Code]={PR}
, [Place of Service]={'Primary Care Locations'}, [Plan Service Provider Specialty Code]={'Specialty Code Values'}, [Procedure Code (CPT/HCPCS)]={'Primary Care Procedure Codes'}>}Visits),[Incurred Year],[Member ID])>0, 1))

Set 2 People with ED (Emergency Department) visit:

sum(if(aggr(sum({$<[Service Category Code]={'OP', 'PR'}, [ER Indicator]={'Y'}>}[Visits]),[Incurred Year],[Member ID])>0, 1))

Sample Data:

Person        Visit Type         Year

A                     ED                     2019

A                    PCP                  2019

B                   ED                     2018

B                  PCP                   2019

C                  PCP                    2020

C                 PCP                    2020

C                 ED                        2020

C                 ED                         2020

Expected Outcome:

Year              Distinct count of members with PCP and ED visit in year                 

2018                    0

2019                   1

2020                   1

What I think is going on, while using AGGR() at an individual level I am getting the correct aggregation, but when I combine the two sets I lose the member level detail.

What I tried and worked/didn't work:

Set 1 * Set 2 If I had member ID it worked magically.  When I remove Member ID I get a multiplication of the visits for ED and PCP.

Thoughts on how to combine the sets to give me the union of people who had at least one PCP visit and one ED visit in the same year?

[Ksrinivasan]

hi,

try this expression in screen

ksrinivasan

[brentski]

That was a really interesting idea.  Unfortunately, Qlik Sense doesn't like the nested aggregation.

What I tried was if aggr(set1)>0,1, aggr(set2)>0,1.

The core of my issue is creating the union between the two sets.  Once I get that, the counting should sort itself out (I hope!)

[brentski · Accepted solution]

I'll describe in words what I did then show the code.  I decided that I needed to find the members by year that had a PCP visit, then I could join that to members by year for ED visits.  I then just needed to do the calculation 3 times (once for each year). 

Here is how I solved it, it was not the way I wanted to do it but it was the way I had to do it:

=if([Incurred Year]=$(=Max([Incurred Year])), count({<[Member ID]={"=sum({<[Incurred Year]={$(=Max([Incurred Year]))}, [Service Category Code]={PR}
, [Place of Service]={'Primary Care Locations'}, [Plan Service Provider Specialty Code]={'Specialty Code Values'}, [Procedure Code (CPT/HCPCS)]={'Primary Care Procedure Codes'}>} Visits)>=0"}
, [Incurred Year]={$(=Max([Incurred Year]))}>
*

<[Member ID]={"=sum({<[Incurred Year]={$(=Max([Incurred Year]))}, [Service Category Code]={'OP', 'PR'}, [ER Indicator]={'Y'}>} Visits)>=0"}
, [Incurred Year]={$(=Max([Incurred Year]))}>} distinct [Member ID])

,(if([Incurred Year]=$(=Max([Incurred Year])-1),

count({<[Member ID]={"=sum({<[Incurred Year]={$(=Max([Incurred Year])-1)}, [Service Category Code]={PR}
, [Place of Service]={'Primary Care Locations'}, [Plan Service Provider Specialty Code]={'Specialty Code Values'}, [Procedure Code (CPT/HCPCS)]={'Primary Care Procedure Codes'}>} Visits)>=0"}
, [Incurred Year]={$(=Max([Incurred Year])-1)}>
*

<[Member ID]={"=sum({<[Incurred Year]={$(=Max([Incurred Year])-1)}, [Service Category Code]={'OP', 'PR'}, [ER Indicator]={'Y'}>} Visits)>=0"}
, [Incurred Year]={$(=Max([Incurred Year])-1)}>} distinct [Member ID])

,(if([Incurred Year]=$(=Max([Incurred Year])-2),

count({<[Member ID]={"=sum({<[Incurred Year]={$(=Max([Incurred Year])-2)}, [[Service Category Code]={PR}
, [Place of Service]={'Primary Care Locations'}, [Plan Service Provider Specialty Code]={'Specialty Code Values'}, [Procedure Code (CPT/HCPCS)]={'Primary Care Procedure Codes'}>} Visits)>=0"}
, [Incurred Year]={$(=Max([Incurred Year])-2)}>
*

<[Member ID]={"=sum({<[Incurred Year]={$(=Max([Incurred Year])-2)}, [Service Category Code]={'OP', 'PR'}, [ER Indicator]={'Y'}>} Visits)>=0"}
, [Incurred Year]={$(=Max([Incurred Year])-2)}>} distinct [Member ID])

)))))

I find this forum to be so very helpful and I really hope posting the solution helps someone else stumble upon a different way to solve a problem.  Sometimes I can't solve it the way I want but the way I have to.