List top n nationalities in a text box

[Anonymous]

Hi everyone,

I have a problem with listing top n values in a text object.

The scenario is to list top 5 nationalities for student enrollment (count(studentID)).

I tried rank and firstsortedvalue, none of it work. Because firstsortedvalue does not accept nested aggregation so I cannot put count(studentID). Besides, it only provide the top 1 value.

Anybody could help please? Thank you very much in advanced!

Regards,

Anni

[Anonymous]

Hi!

You can try this:

Concat(AGGR(IF(Rank(Count(studentID), 4)<=5, NATIONALITY), NATIONALITY) , ', ')

Let me know!

[Anonymous · Accepted solution]

Hi Elena,

Thank you very much. This is really helpful. The only problem is that the sorting is in alphabetic order of Nationality. But I need the sorting by the enrollment. I've changed to below formula and it works fine! Thanks a lot again!

Concat(AGGR(IF(Rank(Count(studentID))<=5, NATIONALITY),NATIONALITY),', ', aggr(rank(Count(studentID) ), NATIONALITY))

Regards,

Anni

[vinieme12]

Hi,

Elena's Expression is correct but it will concatenate in LOAD order and NOT in Order of their ranks.

If you want to concatenate in order of the RANKS, then do the below

//////////////////////////////////////////////////////////

FACT:

LOAD

RowNo() as StudentID,

PICK(Ceil(7*Rand()),'India','UK','Japan','HongKong','Taiwan','Germany','Spain','Turkey','Italy','China') as Nationality

Autogenerate(50);

NoConcatenate

SortOrder:

LOAD

Nationality,

count(StudentID) as SortOrder

RESIDENT FACT

GROUP BY Nationality;

Expression in Textbox

=Concat(AGGR(IF(rank(count(StudentID),1,1)<=5, Nationality),Nationality) , ', ',-SortOrder)

[vinieme12]

if your Query is resolved please close the thread

https://community.qlik.com/docs/DOC-14806

[maxgro]

concat(aggr(if(rank(count(DISTINCT student), 4)<=4, country), country), ',', -aggr(count(DISTINCT student), country))

[Anonymous]

Dear all,

Thanks all for your answers. Elena's answer was half correct and I figure out the way to sort the nationality by the number of students.

I forgot to mark a correct answer maybe that's why the question is still open.

Thanks Vineeth and maxgro for your answers anyway:)

Kind Regards,

Anni