Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
mindyckay
Partner
Partner

Return Dimension with Highest Percentage

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-9189168.5%
10-192012512.4%
20-29320247.5%
30-39315257.9%
40-491541610.4%
50-591400.0%
60-69100.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

12 Replies
ogautier62
Specialist II
Specialist II

Hi

second expression seems good,

but if you've got null it's surely because you have tie (you can check it with your data

(firstsortedvalue returns null with tie)

try :

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

you'll have one of the ties

if you want all ties, use rank()

regards

ogautier62
Specialist II
Specialist II

to display ties

you could try :

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

mindyckay
Partner
Partner
Author

This didn't work.  Returned ... 0-9*40-49

mindyckay
Partner
Partner
Author

This didn't work.  Returned a '-'.

ogautier62
Specialist II
Specialist II

could you share some rows in xlsx to verify expression please ?

mindyckay
Partner
Partner
Author

I've attached the data.  Appreciate all this help.

Thanks,
Mindy

captain89
Creator
Creator

Hi,

maybe you can do it in another simpler way:

1) rank(Count({<complicationYN={'YES'}>} SurgeryID)/Count(SurgeryID))

in this way you rank values

2) go to the chart property and hide the column with the expression

3) set max number of records =1

see attached fileqlikcom7.jpg

mindyckay
Partner
Partner
Author

Hi Matteo,

Thanks for responding but I forgot to mention that this is for Qlik Sense.  So, your solution won't work.

Mindy

nsetty
Partner
Partner

It results two [Age Group] either at the lowest or highest, hence FirstSortedValue returns "-"

agegroup.PNG