5 Replies Latest reply: Apr 23, 2018 5:31 PM by Krishna Nagulapally

# Set analysis with multiple tables

I need to calculate the % of patients with Blood Pressure under 140/90, by year. Seems straightforward, but I'm in need of advice on the best approach to keep only certain patients, based on a comparison of dates from two tables.

My data are in tables with these fields (among others):

• Conditions: PatientID, ConditionStartDate
• BP: PatientID, ObservationDate, Systolic, Diastolic
• Visits: PatientID, VisitDate

I need a bar chart with Year of Visit as the dimension, and % of patients whose last reading of each year was under 140/90. I can include only patients whose ConditionStartDate is earlier than July 1 of the chart Year.

Should I combine the data into a new table so that I can perform the date comparison with set analysis, like this:

EventRowID={"=ConditionStartDate<MakeDate(year(VisitDate), 7, 1)"}

Or should I use the p() function, in my chart measure, with year fields created in the load script, like:

VisitYear=p(BPYear)

Or something else altogether?

• ###### Re: Set analysis with multiple tables

Hi Krishna, thanks for the quick reply.

BP:

PatientIDObservationDateSystolicDiastolic
5454/9/201712276
54510/10/201713980
6783/1/201613479
6782/4/201714191
9105/5/201511969

Visits:

PatientIDVisitDate
5451/9/2016
5454/9/2017
6783/1/2016
6781/10/2017
9105/5/2015

Conditions:

PatientIDConditionStartDate
5452/10/2016
6789/10/2016
91011/11/2010

The bar chart will show Visit Years 2015, 2016, 2017.

Patient 545 will appear in 2016 and 2017 (because his condition started before 7/1 of both years).

Patient 678 will appear in 2017 only (because her condition started after 7/1/2016 but before 7/1/2017).

Patient 910 will appear in 2015 only (because he had no visits in 2016 or 2017).

• ###### Re: Set analysis with multiple tables

Hope this helps/this could be your staring point.

• ###### Re: Set analysis with multiple tables

Thank you so much for your help. So generally, do you put all fields into one table, when you need do filtering that compares fields?

• ###### Re: Set analysis with multiple tables

As i was having 2 fields (you can map or do left join) and the data is straight forward i have merged the two tables.but if theres is granularity of data or if theres is case where you shouldnt merge then just associate those two tables with a common field and do the analysis.