Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Pls try below expression.
FirstSortedValue(DISTINCT [Age Group], -Aggr(count({<complicationYN={'Yes'}>}distinct SurgeryID), [Age Group]), 1)
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 :
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])