Return Dimension with Highest Percentage

[mindyckay]

Hi All,

I need help with some code.  This is for Qlik Sense.

Business Question:

Which age group has the highest percentage of surgery complications?

  - Age Group

  - SurgeryID

  - complicationYN = {'Yes'}

Percentage of surgeries with complications   ... this works

    = Count({<complicationYN = {'Yes'}>} SurgeryID)/Count(SurgeryID)

Age group with the highest count of surgeries      ..... this works

    = FirstSortedValue([Age Group], -Aggr(Count(distinct SurgeryID), [Age Group]))

Age group with the highest count of surgeries with complications  ..... these do NOT work   ... return a '-'

    = FirstSortedValue([Age Group], -Aggr(If(complicationYN = 'Yes', Count(distinct SurgeryID)), [Age Group]))

    = FirstSortedValue([Age Group], -Aggr(Count({<complicationYN = {'Yes'}>} distinct SurgeryID), [Age Group]))

    = If(complicationYN = 'Yes', FirstSortedValue([Age Group], -Aggr(Count(distinct SurgeryID), [Age Group])))

Age group with the highest percentage of surgeries with complications .....

   = ? ? ? ? ? ? ? ? ?

Age Groups	# Surgeries	# Complications	% of Surgeries w/ Complications
0-9	189	16	8.5%
10-19	201	25	12.4%
20-29	320	24	7.5%
30-39	315	25	7.9%
40-49	154	16	10.4%
50-59	14	0	0.0%
60-69	1	0	0.0%
[Age Group]
# Surgeries = Count(SurgeryID)
# Complications =  Count({<complicationYN = {'Yes'}>} SurgeryID)
% of Surgeries w/ Complications =  Count({<complicationYN = {'Yes'}>} SurgeryID) / Count(SurgeryID)
Age Group w/Highest % of Complications =  ? ? ? ? ?

Thanks in advance,

Mindy

[nsetty]

Pls try below expression.

FirstSortedValue(DISTINCT [Age Group], -Aggr(count({<complicationYN={'Yes'}>}distinct SurgeryID), [Age Group]), 1)

[ogautier62]

Hi,

ok, with rank, not minus sign

so with :

=concat( if(aggr(num(rank(count({<complicationYN = {'Yes'}>} distinct SurgeryID))),[Age Group])=1,[Age Group]),'*')

I get :

[timpoismans]

Hi Mindy

Take a look at the following.

First table shows the calculations:

Following is a solution in the table:

Measure: Count({<complicationYN={'Yes'}>}complicationYN)/Count(SurgeryID)

This is done by applying the following settings:

Apply the following to the dimension Age Group:

Apply the following to the sorting of the dimension Age Group:

1. Sort by Expression

2. Use the same measure as the percentages were calculated as the sorting measure

3. Descending

Following is a solution in KPI:

Measure: Aggr(If(Rank((Count({<complicationYN={'Yes'}>}complicationYN)/Count(SurgeryID)))=1,[Age Group]),[Age Group])