topic Re: Get top 5 dimensions in Line chart in App Development https://community.qlik.com/t5/App-Development/Get-top-5-dimensions-in-Line-chart/m-p/1755862#M58290 <P>Hello&nbsp;<a href="https://community.qlik.com/t5/user/viewprofilepage/user-id/336">@Kushal_Chawda</a>&nbsp;This almost worked for me, except for I forgot to mention one more constraint in my data.</P><P>Below is the class assigned to each country;</P><TABLE><TBODY><TR><TD>Country</TD><TD>Status</TD></TR><TR><TD>Australia</TD><TD>Class1</TD></TR><TR><TD>Germany</TD><TD>Class1</TD></TR><TR><TD>Italy</TD><TD>Class2</TD></TR><TR><TD>Jersey</TD><TD>Class1</TD></TR><TR><TD>Luxembourg</TD><TD>Class1</TD></TR><TR><TD>Netherlands</TD><TD>Class2</TD></TR><TR><TD>Singapore</TD><TD>Class2</TD></TR><TR><TD>Switzerland</TD><TD>Class1</TD></TR><TR><TD>United Kingdom</TD><TD>Class2</TD></TR><TR><TD>United States of America</TD><TD>Class1</TD></TR><TR><TD>country_unassigned</TD><TD>Class1</TD></TR><TR><TD>Virgin Islands (Brit)</TD><TD>Class2</TD></TR><TR><TD>Canada</TD><TD>Class1</TD></TR><TR><TD>France</TD><TD>Class2</TD></TR><TR><TD>Japan</TD><TD>Class2</TD></TR></TBODY></TABLE><P>So I need to get Top 5 countries for Class1 countries, I combined your query with another if but its not giving any result:</P><P>=if(Status = 'Class1', aggr(if( rank(sum(Value))&lt;6,Country),Country))</P><P>Can you suggest something here please ?</P> Mon, 26 Oct 2020 12:13:11 GMT Saurabh07 2020-10-26T12:13:11Z Get top 5 dimensions in Line chart https://community.qlik.com/t5/App-Development/Get-top-5-dimensions-in-Line-chart/m-p/1755843#M58285 <P>Hello Gurus,</P><P>I have a dataset with 5 months of values for different countries.&nbsp;</P><TABLE><TBODY><TR><TD>Date</TD><TD>Country</TD><TD>Value</TD></TR><TR><TD>01-01-2020</TD><TD>Australia</TD><TD>4121</TD></TR><TR><TD>01-01-2020</TD><TD>Germany</TD><TD>1092</TD></TR><TR><TD>01-01-2020</TD><TD>Italy</TD><TD>2475</TD></TR><TR><TD>01-01-2020</TD><TD>Jersey</TD><TD>1548</TD></TR><TR><TD>01-01-2020</TD><TD>Luxembourg</TD><TD>2102</TD></TR><TR><TD>01-01-2020</TD><TD>Netherlands</TD><TD>1290</TD></TR><TR><TD>01-01-2020</TD><TD>Singapore</TD><TD>4142</TD></TR><TR><TD>01-01-2020</TD><TD>Switzerland</TD><TD>261528</TD></TR><TR><TD>01-01-2020</TD><TD>United Kingdom</TD><TD>10398</TD></TR><TR><TD>01-01-2020</TD><TD>United States of America</TD><TD>78009</TD></TR><TR><TD>01-02-2020</TD><TD>Australia</TD><TD>5024</TD></TR><TR><TD>01-02-2020</TD><TD>country_unassigned</TD><TD>30105</TD></TR><TR><TD>01-02-2020</TD><TD>Germany</TD><TD>5442</TD></TR><TR><TD>01-02-2020</TD><TD>Luxembourg</TD><TD>4924</TD></TR><TR><TD>01-02-2020</TD><TD>Netherlands</TD><TD>3677</TD></TR><TR><TD>01-02-2020</TD><TD>Singapore</TD><TD>3436</TD></TR><TR><TD>01-02-2020</TD><TD>Switzerland</TD><TD>18535</TD></TR><TR><TD>01-02-2020</TD><TD>United Kingdom</TD><TD>13358</TD></TR><TR><TD>01-02-2020</TD><TD>United States of America</TD><TD>89225</TD></TR><TR><TD>01-02-2020</TD><TD>Virgin Islands (Brit)</TD><TD>4277</TD></TR><TR><TD>01-04-2020</TD><TD>Canada</TD><TD>8823</TD></TR><TR><TD>01-04-2020</TD><TD>country_unassigned</TD><TD>48637</TD></TR><TR><TD>01-04-2020</TD><TD>France</TD><TD>11286</TD></TR><TR><TD>01-04-2020</TD><TD>Germany</TD><TD>11970</TD></TR><TR><TD>01-04-2020</TD><TD>Japan</TD><TD>7714</TD></TR><TR><TD>01-04-2020</TD><TD>Luxembourg</TD><TD>14027</TD></TR><TR><TD>01-04-2020</TD><TD>Netherlands</TD><TD>7277</TD></TR><TR><TD>01-04-2020</TD><TD>Switzerland</TD><TD>112202</TD></TR><TR><TD>01-04-2020</TD><TD>United Kingdom</TD><TD>37741</TD></TR><TR><TD>01-04-2020</TD><TD>United States of America</TD><TD>155936</TD></TR><TR><TD>01-05-2020</TD><TD>Australia</TD><TD>5388</TD></TR><TR><TD>01-05-2020</TD><TD>country_unassigned</TD><TD>318186</TD></TR><TR><TD>01-05-2020</TD><TD>Germany</TD><TD>5636</TD></TR><TR><TD>01-05-2020</TD><TD>Luxembourg</TD><TD>3127</TD></TR><TR><TD>01-05-2020</TD><TD>Netherlands</TD><TD>3327</TD></TR><TR><TD>01-05-2020</TD><TD>Singapore</TD><TD>3498</TD></TR><TR><TD>01-05-2020</TD><TD>Switzerland</TD><TD>20748</TD></TR><TR><TD>01-05-2020</TD><TD>United Kingdom</TD><TD>13776</TD></TR><TR><TD>01-05-2020</TD><TD>United States of America</TD><TD>90066</TD></TR><TR><TD>01-06-2020</TD><TD>Virgin Islands (Brit)</TD><TD>4046</TD></TR><TR><TD>01-06-2020</TD><TD>Australia</TD><TD>3458</TD></TR><TR><TD>01-06-2020</TD><TD>Canada</TD><TD>2739</TD></TR><TR><TD>01-06-2020</TD><TD>country_unassigned</TD><TD>343785</TD></TR><TR><TD>01-06-2020</TD><TD>France</TD><TD>2693</TD></TR><TR><TD>01-06-2020</TD><TD>Germany</TD><TD>5535</TD></TR><TR><TD>01-06-2020</TD><TD>Luxembourg</TD><TD>2450</TD></TR><TR><TD>01-06-2020</TD><TD>Netherlands</TD><TD>2882</TD></TR><TR><TD>01-06-2020</TD><TD>Switzerland</TD><TD>18619</TD></TR><TR><TD>01-06-2020</TD><TD>United Kingdom</TD><TD>9143</TD></TR><TR><TD>01-06-2020</TD><TD>United States of America</TD><TD>88556</TD></TR></TBODY></TABLE><P>I want to see only TOP 5 countries in line chart. But when I plot the same in Qlik, I get unexpected results.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Saurabh07_0-1603711502616.png" style="width: 400px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/43009i4755D1EA3BE5FE92/image-size/medium?v=v2&amp;px=400" role="button" title="Saurabh07_0-1603711502616.png" alt="Saurabh07_0-1603711502616.png" /></span></P><P>I have applied limit on the dimension to get Top 5 only, still 8 countries are being plotted. Not sure why? Can anyone please help me here?</P> Mon, 20 Dec 2021 21:08:46 GMT https://community.qlik.com/t5/App-Development/Get-top-5-dimensions-in-Line-chart/m-p/1755843#M58285 Saurabh07 2021-12-20T21:08:46Z Re: Get top 5 dimensions in Line chart https://community.qlik.com/t5/App-Development/Get-top-5-dimensions-in-Line-chart/m-p/1755852#M58288 <P><a href="https://community.qlik.com/t5/user/viewprofilepage/user-id/127095">@Saurabh07</a>&nbsp; create calculated dimension for Country like below</P><P>=aggr(if( rank(sum(Value))&lt;6,Country),Country)</P><P>Note: check "suppress when value is NULL" option</P> Mon, 26 Oct 2020 11:53:54 GMT https://community.qlik.com/t5/App-Development/Get-top-5-dimensions-in-Line-chart/m-p/1755852#M58288 Kushal_Chawda 2020-10-26T11:53:54Z Re: Get top 5 dimensions in Line chart https://community.qlik.com/t5/App-Development/Get-top-5-dimensions-in-Line-chart/m-p/1755862#M58290 <P>Hello&nbsp;<a href="https://community.qlik.com/t5/user/viewprofilepage/user-id/336">@Kushal_Chawda</a>&nbsp;This almost worked for me, except for I forgot to mention one more constraint in my data.</P><P>Below is the class assigned to each country;</P><TABLE><TBODY><TR><TD>Country</TD><TD>Status</TD></TR><TR><TD>Australia</TD><TD>Class1</TD></TR><TR><TD>Germany</TD><TD>Class1</TD></TR><TR><TD>Italy</TD><TD>Class2</TD></TR><TR><TD>Jersey</TD><TD>Class1</TD></TR><TR><TD>Luxembourg</TD><TD>Class1</TD></TR><TR><TD>Netherlands</TD><TD>Class2</TD></TR><TR><TD>Singapore</TD><TD>Class2</TD></TR><TR><TD>Switzerland</TD><TD>Class1</TD></TR><TR><TD>United Kingdom</TD><TD>Class2</TD></TR><TR><TD>United States of America</TD><TD>Class1</TD></TR><TR><TD>country_unassigned</TD><TD>Class1</TD></TR><TR><TD>Virgin Islands (Brit)</TD><TD>Class2</TD></TR><TR><TD>Canada</TD><TD>Class1</TD></TR><TR><TD>France</TD><TD>Class2</TD></TR><TR><TD>Japan</TD><TD>Class2</TD></TR></TBODY></TABLE><P>So I need to get Top 5 countries for Class1 countries, I combined your query with another if but its not giving any result:</P><P>=if(Status = 'Class1', aggr(if( rank(sum(Value))&lt;6,Country),Country))</P><P>Can you suggest something here please ?</P> Mon, 26 Oct 2020 12:13:11 GMT https://community.qlik.com/t5/App-Development/Get-top-5-dimensions-in-Line-chart/m-p/1755862#M58290 Saurabh07 2020-10-26T12:13:11Z Re: Get top 5 dimensions in Line chart https://community.qlik.com/t5/App-Development/Get-top-5-dimensions-in-Line-chart/m-p/1755869#M58291 <P><a href="https://community.qlik.com/t5/user/viewprofilepage/user-id/127095">@Saurabh07</a>&nbsp; try below</P><P><SPAN>=aggr(if( rank(sum({&lt;status={"Class1"}&gt;}Value))&lt;6,Country),Country)</SPAN></P> Mon, 26 Oct 2020 12:19:08 GMT https://community.qlik.com/t5/App-Development/Get-top-5-dimensions-in-Line-chart/m-p/1755869#M58291 Kushal_Chawda 2020-10-26T12:19:08Z Re: Get top 5 dimensions in Line chart https://community.qlik.com/t5/App-Development/Get-top-5-dimensions-in-Line-chart/m-p/1755875#M58292 <P><a href="https://community.qlik.com/t5/user/viewprofilepage/user-id/336">@Kushal_Chawda</a>&nbsp;It worked like magic, I need to work a lot on my set analysis knowledge to be able to write such expressions in first place! Thanks a ton Man!</P> Mon, 26 Oct 2020 12:28:03 GMT https://community.qlik.com/t5/App-Development/Get-top-5-dimensions-in-Line-chart/m-p/1755875#M58292 Saurabh07 2020-10-26T12:28:03Z Re: Get top 5 dimensions in Line chart https://community.qlik.com/t5/App-Development/Get-top-5-dimensions-in-Line-chart/m-p/1755900#M58298 <P>Hello&nbsp;<a href="https://community.qlik.com/t5/user/viewprofilepage/user-id/336">@Kushal_Chawda</a>, there is another doubt here if I may ask, what if I want to exclude Australia and Jersey all together from my calculations. I can think of -= function here , but not sure how?</P> Mon, 26 Oct 2020 13:26:44 GMT https://community.qlik.com/t5/App-Development/Get-top-5-dimensions-in-Line-chart/m-p/1755900#M58298 Saurabh07 2020-10-26T13:26:44Z