# Qlik Sense App Development

Contributor II

## 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?

Tags (1)
1 Solution

Accepted Solutions
Valued Contributor III

## Re: Set analysis with multiple tables

Hope this helps/this could be your staring point.

5 Replies
Valued Contributor III

Contributor II

## 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).

Valued Contributor III

## Re: Set analysis with multiple tables

Hope this helps/this could be your staring point.

Contributor II

## 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?

Highlighted
Valued Contributor III

## 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.