https://community.qlik.com/t5/QlikView/Show-correct-ranking-in-a-pivot-table/m-p/1578489#M442708 <P>Hi Friends,</P><P>I got stuck in a rank calculation. I am using a pivot table where 3 dimensions are there Product, Sub-Product and City.</P><P>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.&nbsp;</P><P>For DIM 2, City 4 rank should be 2, but its showing 4.</P><P>I am using rank(expr, 1, 1) in the expression</P><P>Product &nbsp;&nbsp; Sub_Product &nbsp;&nbsp; City &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Performance &nbsp; &nbsp; &nbsp; Rank</P><P>A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; City1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 100% &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1</P><P>A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<SPAN>City2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 100% &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1</SPAN></P><P>A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<SPAN>City3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 100% &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1</SPAN></P><P>A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<SPAN>City4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 98% &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <FONT color="#ff0000">4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ( it should be 2)</FONT></SPAN></P><P>Please let me know, if any further information required.</P><P>Thanks in advance.</P><P>Regards,</P><P>Chinmaya</P> Sat, 16 Nov 2024 03:37:41 GMT ckmchinmaya 2024-11-16T03:37:41Z https://community.qlik.com/t5/QlikView/Show-correct-ranking-in-a-pivot-table/m-p/1578489#M442708 <P>Hi Friends,</P><P>I got stuck in a rank calculation. I am using a pivot table where 3 dimensions are there Product, Sub-Product and City.</P><P>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.&nbsp;</P><P>For DIM 2, City 4 rank should be 2, but its showing 4.</P><P>I am using rank(expr, 1, 1) in the expression</P><P>Product &nbsp;&nbsp; Sub_Product &nbsp;&nbsp; City &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Performance &nbsp; &nbsp; &nbsp; Rank</P><P>A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; City1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 100% &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1</P><P>A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<SPAN>City2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 100% &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1</SPAN></P><P>A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<SPAN>City3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 100% &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1</SPAN></P><P>A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<SPAN>City4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 98% &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <FONT color="#ff0000">4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ( it should be 2)</FONT></SPAN></P><P>Please let me know, if any further information required.</P><P>Thanks in advance.</P><P>Regards,</P><P>Chinmaya</P> Sat, 16 Nov 2024 03:37:41 GMT https://community.qlik.com/t5/QlikView/Show-correct-ranking-in-a-pivot-table/m-p/1578489#M442708 ckmchinmaya 2024-11-16T03:37:41Z https://community.qlik.com/t5/QlikView/Show-correct-ranking-in-a-pivot-table/m-p/1578686#M442711 <P>try this:</P><P>&nbsp;</P><LI-CODE lang="markup">Div( Index( '-' &amp; 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' ) ) ,5)+1</LI-CODE><P>&nbsp;</P> Thu, 09 May 2019 19:45:09 GMT https://community.qlik.com/t5/QlikView/Show-correct-ranking-in-a-pivot-table/m-p/1578686#M442711 Frank_Hartmann 2019-05-09T19:45:09Z