Skip to main content
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Show correct ranking in a pivot table

Hi Friends,

I got stuck in a rank calculation. I am using a pivot table where 3 dimensions are there Product, Sub-Product and City.

One expression is calculating Achievement % and Rank of that against all 4 cities. The Rank column is working fine where all are unique values. It get stuck when two/more values are same as shown in the table below. 

For DIM 2, City 4 rank should be 2, but its showing 4.

I am using rank(expr, 1, 1) in the expression

Product    Sub_Product    City             Performance       Rank

A                   A1                         City1            100%                       1

A                   A1                         City2            100%                       1

A                   A1                         City3            100%                       1

A                   A1                         City4             98%                         4           ( it should be 2)

Please let me know, if any further information required.

Thanks in advance.



Labels (2)
1 Reply
Master II
Master II

try this:




  '-' & Concat(DISTINCT TOTAL num(aggr(rank(sum(Performance),1,1),City), '0000'), '-', aggr(rank(sum(Performance),City),City)),

  num(aggr(rank(sum(Performance),City),City),'0000' )