New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Need Rank on Sum of Ranks

I have 4 columns that I am using to create ranks from other measures:

For the 4 columns I am using the below expression:

Aggr(

Rank(

\$(variable)

4,1),

Rep)

I then add the 4 columns together with the below expression into "Sum of Rank"

Column(6) + Column(7) + Column(8) + Column(9) (stored in variable "Sum_of_Rank")

Then, in the column "Final Rank", I am attempting to rank on the "Sum of Rank" with the below expression.

Aggr(

Rank(

\$(Sum_of_Rank),

4,1),

Rep)

However, this is not working. I believe the problem is traced back to the Columns being added in the measure. Any ideas? Some data below

 QTD Power Ranking QTD Power Ranking QoQ QTD Power Ranking QoQ% QTD PTQ Ranking Sum of Rank Final Rank 4 4 4 13 25 - 7 6 6 29 48 - 14 16 16 2 48 - 23 13 13 15 64 - 2 5 5 53 65 - 13 9 9 41 72 - 5 8 8 58 79 - 6 28 28 19 81 - 21 20 20 34 95 - 30 25 25 16 96 - 3 29 29 38 99 -
1 Solution

Accepted Solutions
Not applicable

Re: Need Rank on Sum of Ranks

Well, you cannot really use Column reference within the Aggr() function... you need to use the exact expressions

8 Replies
Not applicable

Re: Need Rank on Sum of Ranks

May be try without dollar sign expansion

Aggr(

Rank(

Sum_of_Rank,

4,1),

Rep)

Not applicable

Re: Need Rank on Sum of Ranks

Hi Sunny, thats not working, I added the formula in the variable directly to the expression and it still does not work. See below.

Aggr(

Rank(

Column(6) + Column(7) + Column(8) + Column(9),

4,1),

[Rep])

Not applicable

Re: Need Rank on Sum of Ranks

Well, you cannot really use Column reference within the Aggr() function... you need to use the exact expressions

Not applicable

Re: Need Rank on Sum of Ranks

Ok, thanks. thats the problem. In place I pasted in all the expression from the 4 columns and that worked. Thanks so much Sunny!

Not applicable

Re: Need Rank on Sum of Ranks

Sunny,

I added all the expressions togther from those columns like you suggested and it seems to be working. However, I actually want to do the reverse ranking. For example:

the numbers for 4 ranking columns are 4+4+5+13 which equals a sum of rankings of 26, which is the lowest sum of all the corresponding ranking columns, and therefore has the best Power Ranking.

on the flip side: 127+130+132+104=493 has highest sum of all the corresponding ranking columns, and therefore has the worst Power Ranking.

However, this PowerRankning expresion below is ranking based on the hihest number, so therefore 493 is the 1 ranking while 26 is 135. I want to flip this around.

Aggr(

Rank(

Aggr(Rank(\$(vGSPRanking_Current_FY_QTD_Sales),),[Rep])

+

Aggr(Rank(\$(vGSPRanking_Current_FY_QTD_Sales)-\$(vGSPRanking_Prior_YQTD),4,1),[Rep])

+

Aggr(Rank((\$(vGSPRanking_Current_FY_QTD_Sales)-\$(vGSPRanking_Prior_YQTD))/\$(vGSPRanking_Prior_YQTD),4),[Rep])

+

Aggr(Rank(sum([Shipped])/Sum([Quota])*\$(=\$(vExp_Pct_CurrQtr_TD)),4),[Rep]),

4,1,),

[ Rep])

Any ideas?

Not applicable

Re: Need Rank on Sum of Ranks

May be add minus sign in front of all your expressions or multiple the sum by -1

Not applicable

Re: Need Rank on Sum of Ranks

That worked! thanks!

Not applicable

Re: Need Rank on Sum of Ranks

Awesome

Community Browser