topic Re: Rank Question in QlikView https://community.qlik.com/t5/QlikView/Rank-Question/m-p/571597#M212960 <HTML><HEAD></HEAD><BODY><P>try this in pivot table</P><P>Dim1 -Month</P><P>Dim2&nbsp; =aggr(if(rank(Quantity)=1, Product),Month,Product)</P><P>check on suppress null value</P><P>expression</P><P>Max(Quantity)</P></BODY></HTML> Thu, 20 Mar 2014 10:32:50 GMT er_mohit 2014-03-20T10:32:50Z Rank Question https://community.qlik.com/t5/QlikView/Rank-Question/m-p/571593#M212956 <HTML><HEAD></HEAD><BODY><P>I have the following data set:</P><P>Month&nbsp;&nbsp;&nbsp;&nbsp; Product&nbsp;&nbsp;&nbsp;&nbsp; Quantity</P><P>Jan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1000</P><P>Feb&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 500</P><P>Mar&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100</P><P>Jan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 500</P><P>Feb&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 900</P><P>Mar&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200</P><P>Jan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200</P><P>Feb&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 300</P><P>Mar&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 700</P><P></P><P>The results I want is:</P><P></P><TABLE><TBODY><TR><TD>Month </TD><TD>Product </TD><TD>Quantity</TD></TR></TBODY></TABLE><P>Jan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1000</P><P>Feb&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 900</P><P>Mar&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 700</P><P></P><P>In other words I want to rank the top product quantity by month.&nbsp; I tried the following:</P><P> 1.&nbsp; In the dimension - Month and =if(aggr(rank(Quantity),Month,Product)=1,Quantity)</P><P>2.&nbsp; In the expression - sum(Quantity)</P><P></P><P>It does not seem to work.&nbsp; Can someone advise. </P><P></P><P>David</P></BODY></HTML> Thu, 13 Mar 2014 12:20:34 GMT https://community.qlik.com/t5/QlikView/Rank-Question/m-p/571593#M212956 2014-03-13T12:20:34Z Re: Rank Question https://community.qlik.com/t5/QlikView/Rank-Question/m-p/571594#M212957 <HTML><HEAD></HEAD><BODY><P>Dimension = Month</P><P>Expressions </P><P>FirstSortedValue(Product, -Quantity)</P><P>FirstSortedValue(Quantity, -Quantity)</P></BODY></HTML> Thu, 13 Mar 2014 12:27:23 GMT https://community.qlik.com/t5/QlikView/Rank-Question/m-p/571594#M212957 MK_QSL 2014-03-13T12:27:23Z Re: Rank Question https://community.qlik.com/t5/QlikView/Rank-Question/m-p/571595#M212958 <HTML><HEAD></HEAD><BODY><P>see attached</P><P></P><P>regards</P></BODY></HTML> Thu, 13 Mar 2014 12:31:29 GMT https://community.qlik.com/t5/QlikView/Rank-Question/m-p/571595#M212958 martinpohl 2014-03-13T12:31:29Z Re: Rank Question https://community.qlik.com/t5/QlikView/Rank-Question/m-p/571596#M212959 <HTML><HEAD></HEAD><BODY><P>Can you close this thread please?</P></BODY></HTML> Thu, 20 Mar 2014 10:10:32 GMT https://community.qlik.com/t5/QlikView/Rank-Question/m-p/571596#M212959 MK_QSL 2014-03-20T10:10:32Z Re: Rank Question https://community.qlik.com/t5/QlikView/Rank-Question/m-p/571597#M212960 <HTML><HEAD></HEAD><BODY><P>try this in pivot table</P><P>Dim1 -Month</P><P>Dim2&nbsp; =aggr(if(rank(Quantity)=1, Product),Month,Product)</P><P>check on suppress null value</P><P>expression</P><P>Max(Quantity)</P></BODY></HTML> Thu, 20 Mar 2014 10:32:50 GMT https://community.qlik.com/t5/QlikView/Rank-Question/m-p/571597#M212960 er_mohit 2014-03-20T10:32:50Z