topic Re: Help with Ranking in straight table in QlikView https://community.qlik.com/t5/QlikView/Help-with-Ranking-in-straight-table/m-p/806405#M284689 <HTML><HEAD></HEAD><BODY><P>Overall Rank is the Ranking for the total number of Distinct Doc ID.&nbsp; Year Rank is the Ranking for the number of Distinct Doc id for a given year.</P></BODY></HTML> Fri, 27 Feb 2015 22:07:47 GMT fcecconi 2015-02-27T22:07:47Z Help with Ranking in straight table https://community.qlik.com/t5/QlikView/Help-with-Ranking-in-straight-table/m-p/806403#M284687 <HTML><HEAD></HEAD><BODY><P><SPAN style="font-size: 10pt;">I am trying to create and overall ranking for a publication and then a ranking by year.&nbsp; The overall ranking is working fine.&nbsp; Here is my calculated dimension:&nbsp; Overall Rank <SPAN>=</SPAN><SPAN style="color: #0000ff;">Aggr</SPAN><SPAN>(</SPAN><SPAN style="color: #0000ff;">Num</SPAN><SPAN>(</SPAN><SPAN style="color: #0000ff;">Rank</SPAN><SPAN>(</SPAN><SPAN style="color: #0000ff;">Sum</SPAN><SPAN>(</SPAN><SPAN style="color: #0000ff;">Aggr</SPAN><SPAN>(</SPAN><SPAN style="color: #0000ff;">count</SPAN><SPAN>(</SPAN><SPAN style="color: #0000ff;">DISTINCT</SPAN><SPAN> </SPAN><SPAN style="color: #800000;">DocID</SPAN><SPAN>),</SPAN><SPAN style="color: #800000;">PubName</SPAN><SPAN>)),4,1),'##'),</SPAN><SPAN style="color: #800000;">PubName</SPAN><SPAN>) .</SPAN></SPAN></P><P> </P><P><SPAN>I would like a Ranking per year.</SPAN></P><P> </P><P></P><TABLE border="1" class="jiveBorder" style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; WIDTH: 100%; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;"><STRONG>Year</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;"><STRONG>PubName</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;"><STRONG>Overall Rank</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;"><STRONG>Year Rank</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;"><STRONG># of Pubs (DocId)</STRONG></TH></TR><TR><TD style="padding: 2px;"><P>2000</P></TD><TD style="padding: 2px;">A</TD><TD style="padding: 2px;">1</TD><TD style="padding: 2px;">1</TD><TD style="padding: 2px;">25</TD></TR><TR><TD style="padding: 2px;">2000</TD><TD style="padding: 2px;">B</TD><TD style="padding: 2px;">2</TD><TD style="padding: 2px;">2</TD><TD style="padding: 2px;">15</TD></TR><TR><TD style="padding: 2px;">2000</TD><TD style="padding: 2px;">C</TD><TD style="padding: 2px;">3</TD><TD style="padding: 2px;">3</TD><TD style="padding: 2px;">10</TD></TR><TR><TD style="padding: 2px;">2001</TD><TD style="padding: 2px;">A</TD><TD style="padding: 2px;">1</TD><TD style="padding: 2px;">1</TD><TD style="padding: 2px;">10</TD></TR><TR><TD style="padding: 2px;">2001</TD><TD style="padding: 2px;">B</TD><TD style="padding: 2px;">2</TD><TD style="padding: 2px;">3</TD><TD style="padding: 2px;">2</TD></TR><TR><TD style="padding: 2px;">2001</TD><TD style="padding: 2px;">C</TD><TD style="padding: 2px;">3</TD><TD style="padding: 2px;">2</TD><TD style="padding: 2px;">5</TD></TR><TR><TD style="padding: 2px;">2002</TD><TD style="padding: 2px;">B</TD><TD style="padding: 2px;">2</TD><TD style="padding: 2px;">2</TD><TD style="padding: 2px;">9</TD></TR><TR><TD style="padding: 2px;">2002</TD><TD style="padding: 2px;">C</TD><TD style="padding: 2px;">3</TD><TD style="padding: 2px;">1</TD><TD style="padding: 2px;">10</TD></TR></TBODY></TABLE><P></P><P> </P><P><SPAN></SPAN></P><P></P><P><SPAN><SPAN style="font-size: 10pt;"><SPAN><BR /> </SPAN></SPAN></SPAN></P><P></P><P></P></BODY></HTML> Fri, 27 Feb 2015 21:44:33 GMT https://community.qlik.com/t5/QlikView/Help-with-Ranking-in-straight-table/m-p/806403#M284687 fcecconi 2015-02-27T21:44:33Z Re: Help with Ranking in straight table https://community.qlik.com/t5/QlikView/Help-with-Ranking-in-straight-table/m-p/806404#M284688 <HTML><HEAD></HEAD><BODY><P>What is Overall Rank vs Year Rank?</P><P></P><P>Create a Straight Table</P><P>Dimension</P><P>Year</P><P>PubName</P><P></P><P>Expression</P><P>Sum(DocId)</P><P>Rank(Sum(DocId),4)</P></BODY></HTML> Fri, 27 Feb 2015 21:59:43 GMT https://community.qlik.com/t5/QlikView/Help-with-Ranking-in-straight-table/m-p/806404#M284688 MK_QSL 2015-02-27T21:59:43Z Re: Help with Ranking in straight table https://community.qlik.com/t5/QlikView/Help-with-Ranking-in-straight-table/m-p/806405#M284689 <HTML><HEAD></HEAD><BODY><P>Overall Rank is the Ranking for the total number of Distinct Doc ID.&nbsp; Year Rank is the Ranking for the number of Distinct Doc id for a given year.</P></BODY></HTML> Fri, 27 Feb 2015 22:07:47 GMT https://community.qlik.com/t5/QlikView/Help-with-Ranking-in-straight-table/m-p/806405#M284689 fcecconi 2015-02-27T22:07:47Z Re: Help with Ranking in straight table https://community.qlik.com/t5/QlikView/Help-with-Ranking-in-straight-table/m-p/806406#M284690 <HTML><HEAD></HEAD><BODY><P>Please check the solution provided.. Is this what you are looking for? If not... kindly provide your expected result..</P></BODY></HTML> Fri, 27 Feb 2015 22:14:05 GMT https://community.qlik.com/t5/QlikView/Help-with-Ranking-in-straight-table/m-p/806406#M284690 MK_QSL 2015-02-27T22:14:05Z Re: Help with Ranking in straight table https://community.qlik.com/t5/QlikView/Help-with-Ranking-in-straight-table/m-p/806407#M284691 <HTML><HEAD></HEAD><BODY><P>Hi Frank,</P><P></P><P>i hope the table below will help you </P><P></P><TABLE bgcolor="#ffffff" border="1" cellpadding="2" cellspacing="0" style="font-size: 9pt;"><TBODY><TR bgcolor="#f5f5f5"><TH nowrap="nowrap"><SPAN style="color: #363636;"><STRONG>Year</STRONG></SPAN><STRONG> </STRONG></TH><TH nowrap="nowrap"><SPAN style="color: #363636;"><STRONG>PubName</STRONG></SPAN><STRONG> </STRONG></TH><TH nowrap="nowrap"><SPAN style="color: #363636;"><STRONG>Sum ([# of Pubs (DocId)])</STRONG></SPAN><STRONG> </STRONG></TH><TH nowrap="nowrap"><SPAN style="color: #363636;"><STRONG>=Rank(TOTAL [# of Pubs (DocId)],4,1)</STRONG></SPAN><STRONG> </STRONG></TH><TH nowrap="nowrap"><SPAN style="color: #363636;"><STRONG>=Rank( [# of Pubs (DocId)],4,1)</STRONG></SPAN><STRONG> </STRONG></TH></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2000</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">A</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">25</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">1</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">1</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2000</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">B</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">15</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2000</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">C</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">10</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">3</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">3</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2001</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">A</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">10</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">3</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">1</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2001</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">B</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">8</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">3</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2001</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">C</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">5</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">7</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2002</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">B</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">9</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">6</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2002</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">C</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">10</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">3</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">1</SPAN></TD></TR></TBODY></TABLE></BODY></HTML> Fri, 27 Feb 2015 23:09:09 GMT https://community.qlik.com/t5/QlikView/Help-with-Ranking-in-straight-table/m-p/806407#M284691 mehmet_caldak 2015-02-27T23:09:09Z Re: Help with Ranking in straight table https://community.qlik.com/t5/QlikView/Help-with-Ranking-in-straight-table/m-p/806408#M284692 <HTML><HEAD></HEAD><BODY><P>I described a&nbsp; number of interesting issues related to ranking in my blog post - please read it here:</P><P></P><P><A href="http://www.naturalsynergies.com/q-tip-8-ranking-the-unrankable/" title="http://www.naturalsynergies.com/q-tip-8-ranking-the-unrankable/">Q-Tip #8 – Ranking the Unrankable | Natural Synergies</A></P><P></P><P>cheers,</P><P></P><P>Oleg Troyansky</P><P>www.masterssummit.com - take your QlikView skills to the next level!</P></BODY></HTML> Sat, 28 Feb 2015 00:17:59 GMT https://community.qlik.com/t5/QlikView/Help-with-Ranking-in-straight-table/m-p/806408#M284692 Oleg_Troyansky 2015-02-28T00:17:59Z