2nd or 3rd highest value - Qlik Community

Anonymous · ‎2017-12-20

I have a list of categories each with an average score 'avg(EasyScore)'. I can use the first sorted value to generate the category with the highest average score but is there a way of getting the 2nd, 3rd or nth highest score. Maybe a combination of rank and agrr? The category field is 'SubSubCategory'.

marcus_sommer · ‎2017-12-20

By firstsortedvalue() and by max() could by using the fourth respectively second parameter any other value be returned.

- Marcus

Anonymous · ‎2017-12-21

FirstSortedValue(SubSubCategory, -aggr(avg(EasyScore),SubSubCategory)) would give me the highest SubSubCategory by EasyScore. Where would i use max to give me the second highest?

marcus_sommer · ‎2017-12-21

The logic is:

firstsortedvalue(ReturnField, LookupExpression, SortField, RankNumber)

max(Field, RankNumber)

- Marcus