Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Partner
Partner

Question Using AGGR() in Set Analysis Search

I have a table that stores patient vital information.  In that table, there is a "BMI Class" column that shows the class with possible values of Healthy, Overweight, and Underweight and a "Vitals Rank" column that tells me for each patient, which is the latest vital.  Rank 1 is the most recent, Rank 2 is the 2nd most recent.  When collecting vitals for a patient, they may not enter the required information to calculate BMI, so sometimes the BMI Class field will be null.

I'm trying to create a bar chart that shows the count of patient in each BMI class.  To do this, I need to find the most recent (based on lowest rank) non-null entry in the BMI Class Field for each patient.  In my bar chart, my dimension is [Vital Signs.BMI Class] and my expression is this:

count(distinct
{<
[Vital Signs.Vital Batch ID] =
{
"=AGGR(
If( [Vital Signs.Vitals Rank] =
Min(
{<[Vital Signs.BMI Class]={'Healthy', 'Underweight', 'Overweight'}>}
TOTAL <[Client ID]> [Vital Signs.Vitals Rank]
),
[Vital Signs.Vital Batch ID]
  ),
[Client ID], [Vital Signs.Vital Batch ID])"
}
>}
[Client ID]

Here's a sample of what data in my Vitals table may look like.  Based on this data, i'd expect to see a count of 2 clients in the Healthy category ([ClientID] 1 & 3) and one count in the Overweight category ([ClientID] 2).  I never get any data to return in the chart, so i think there is something wrong with my subquery/search set analysis.

Client IDVital Signs.Vital Batch IDVital Signs.BMI ClassVital Signs.Vitals Rank
11234-1
11235Healthy2
11236-3
11237Overweight4
11238-5
11239Underweight6
21240Overweight1
21241-2
21242Healthy3
31243-1
31244-2
31245-3
31246Healthy4
31247Overweight5
31248-6

Any help would be very much appreciated!  Thanks!

Tags (1)
3 Replies
Highlighted

Re: Question Using AGGR() in Set Analysis Search

Try a slightly different approach

Dimension

=Aggr(FirstSortedValue({<[Vital Signs.BMI Class]={'Healthy', 'Underweight', 'Overweight'}>} [Vital Signs.BMI Class], [Vital Signs.Vitals Rank]), [Client ID])

Expression

=Count(DISTINCT [Client ID])

Capture.PNG

Highlighted

Re: Question Using AGGR() in Set Analysis Search

Here is the expression which seems to work with set analysis search string

=Count(DISTINCT {<[Vital Signs.Vital Batch ID] = {"=[Vital Signs.Vitals Rank] = Aggr(Min(TOTAL <[Client ID]>{<[Vital Signs.BMI Class]={'Healthy', 'Underweight', 'Overweight'}>} [Vital Signs.Vitals Rank]), [Client ID], [Vital Signs.Vitals Rank])"}>}  [Client ID])

Highlighted
Partner
Partner

Re: Question Using AGGR() in Set Analysis Search

Yes, this works perfectly, thank you so much!