12 Replies Latest reply: Jun 26, 2015 4:14 AM by Shiva Reddy

# Some of Top 3 for each country

Hi All,

I have sample data as shown below.

I want to create a straight table with country as dimension. I want to show sum sales for each country, but only the top 3 sales.

Country     Sale

A               5

A               2

A               3

A               7

A               1

B               9

B               2

B               3

B               12

B               4

C               15

C               2

C               1

C               7

C               8

Desired Output

A     15

B     25

C     30

How to achieve this?

• ###### Re: Some of Top 3 for each country

Hi Ankit,

You can use Rank function.

Syntax:

=rank(sum(Investment),1,1)

AND

=aggr(if(Rank(sum(Investment))<=3,Investment),Investment) (For calculating Top 3)

AND

=if(rank(sum(Investment),1,1)=1,'# 1')

Thanks,

AS

• ###### Re: Some of Top 3 for each country

none of these seem to work for me..

• ###### Re: Some of Top 3 for each country

Which version of Qlik do you have?

There is an option (Dimension Limit) in the latest version that easily allow you to obtain what you need ...

• ###### Re: Some of Top 3 for each country

Hi try like this:

IF(Aggr(Rank(sum(Sale)),Country)<=3,sum(Sales))

• ###### Re: Some of Top 3 for each country

Hey, I don't get the desired result.. it sums up all the values.

• ###### Re: Some of Top 3 for each country

Check attachments

Thanks,
AS

• ###### Re: Some of Top 3 for each country

If you interested in the distinct Top 3 values, try this in a chart with dimension Country:

=Rangesum( Max(Sale), Max(Sale,2),Max(Sale,3) )

• ###### Re: Some of Top 3 for each country

Hey,

That works perfectly. But is there an alternative using Rank function?(just to make it dynamic like if its top 10)

• ###### Re: Some of Top 3 for each country

Maybe like this:

=sum(aggr( if(rank(Sale)<=3,Sale),Country,Sale))

• ###### Re: Some of Top 3 for each country

This is perfect... Thank you.

• ###### Re: Some of Top 3 for each country

Ankit,

Like this???

Thanks,
AS

• ###### Re: Some of Top 3 for each country

pfa,