Get top 5 dimensions in Line chart

[Saurabh07]

Hello Gurus,

I have a dataset with 5 months of values for different countries. 

Date	Country	Value
01-01-2020	Australia	4121
01-01-2020	Germany	1092
01-01-2020	Italy	2475
01-01-2020	Jersey	1548
01-01-2020	Luxembourg	2102
01-01-2020	Netherlands	1290
01-01-2020	Singapore	4142
01-01-2020	Switzerland	261528
01-01-2020	United Kingdom	10398
01-01-2020	United States of America	78009
01-02-2020	Australia	5024
01-02-2020	country_unassigned	30105
01-02-2020	Germany	5442
01-02-2020	Luxembourg	4924
01-02-2020	Netherlands	3677
01-02-2020	Singapore	3436
01-02-2020	Switzerland	18535
01-02-2020	United Kingdom	13358
01-02-2020	United States of America	89225
01-02-2020	Virgin Islands (Brit)	4277
01-04-2020	Canada	8823
01-04-2020	country_unassigned	48637
01-04-2020	France	11286
01-04-2020	Germany	11970
01-04-2020	Japan	7714
01-04-2020	Luxembourg	14027
01-04-2020	Netherlands	7277
01-04-2020	Switzerland	112202
01-04-2020	United Kingdom	37741
01-04-2020	United States of America	155936
01-05-2020	Australia	5388
01-05-2020	country_unassigned	318186
01-05-2020	Germany	5636
01-05-2020	Luxembourg	3127
01-05-2020	Netherlands	3327
01-05-2020	Singapore	3498
01-05-2020	Switzerland	20748
01-05-2020	United Kingdom	13776
01-05-2020	United States of America	90066
01-06-2020	Virgin Islands (Brit)	4046
01-06-2020	Australia	3458
01-06-2020	Canada	2739
01-06-2020	country_unassigned	343785
01-06-2020	France	2693
01-06-2020	Germany	5535
01-06-2020	Luxembourg	2450
01-06-2020	Netherlands	2882
01-06-2020	Switzerland	18619
01-06-2020	United Kingdom	9143
01-06-2020	United States of America	88556

I want to see only TOP 5 countries in line chart. But when I plot the same in Qlik, I get unexpected results.

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?

[Kushal_Chawda]

@Saurabh07  create calculated dimension for Country like below

=aggr(if( rank(sum(Value))<6,Country),Country)

Note: check "suppress when value is NULL" option

[Saurabh07]

Hello @Kushal_Chawda This almost worked for me, except for I forgot to mention one more constraint in my data.

Below is the class assigned to each country;

Country	Status
Australia	Class1
Germany	Class1
Italy	Class2
Jersey	Class1
Luxembourg	Class1
Netherlands	Class2
Singapore	Class2
Switzerland	Class1
United Kingdom	Class2
United States of America	Class1
country_unassigned	Class1
Virgin Islands (Brit)	Class2
Canada	Class1
France	Class2
Japan	Class2

So I need to get Top 5 countries for Class1 countries, I combined your query with another if but its not giving any result:

=if(Status = 'Class1', aggr(if( rank(sum(Value))<6,Country),Country))

Can you suggest something here please ?

[Kushal_Chawda · Accepted solution]

@Saurabh07  try below

=aggr(if( rank(sum({<status={"Class1"}>}Value))<6,Country),Country)

[Saurabh07]

@Kushal_Chawda 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!

[Saurabh07]

Hello @Kushal_Chawda, 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?