QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for
Did you mean:
Contributor II

List top n nationalities in a text box

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.

Regards,

Anni

1 Solution

Accepted Solutions
Contributor II
Author

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

6 Replies
Specialist

Hi!

You can try this:

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

Let me know!

Contributor II
Author

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

Champion II

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:

RowNo() as StudentID,

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

Autogenerate(50);

NoConcatenate

SortOrder:

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)

Champion II

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

MVP

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

Contributor II
Author

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.