topic Re: Text box, ranking and set analysis in QlikView

Text box, ranking and set analysis

Tue, 26 Aug 2014 16:17:38 GMT

Hi All,

Our data includes classes with students and their relative rank per class.

Each student attends only 1 class.

Class Student_id Student_rank_in_class

In another table, each student has the tuition fee paid:

Student_id Fee

We want to display in a text box the sum of the fees of the top-ranking students per class.

In another text box the sum of fees paid by the 2nd-ranking students per class.

We know how to do it in a straight table, but face some trouble with the text boxes.

We tried using FirstSortedValue in set-analysis expressions with internal AGGR, but it seems that using a set analysis dimension limit such as DIMENSION = "{$(AGGR(...))}" does not work.

Thanks!

Re: Text box, ranking and set analysis

NickHoff — Tue, 26 Aug 2014 16:26:41 GMT

Text box: =SUM({$<Rank={'1'}>}Fees) Will display the sum of the fees for the student where rank = 1

=SUM({$<Rank={'1'}>}Fees) Will display the sum of the fees for the student where rank = 2

Re: Text box, ranking and set analysis

Anonymous — Tue, 26 Aug 2014 22:33:37 GMT

What if the ranks were not 1,2,3 but rather arbitrary numeric average grades (let's assume without repetitions)?

Re: Text box, ranking and set analysis

NickHoff — Wed, 27 Aug 2014 14:10:23 GMT

The same concept with set analysis would apply, but instead of 1 you would use the range of your average grades. So assuming 90-100 is an A. You would =SUM({$<Rank={'>=90'}>}Fees), if it was 80 through 89 is a B you would add an additional set.=SUM({$<Rank={'>=80'},Rank={'<=89'}>}Fees)