Some of Top 3 for each country

[ankit777]

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?

[amit_saini]

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

[alexandros17]

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 ...

[prajapatiamar38]

Hi try like this:

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

Hope this will help you

[amit_saini]

Check attachments

Thanks,
AS

[swuehl]

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) )

[ankit777]

none of these seem to work for me..

[ankit777]

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

[ankit777]

Hey,

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

[swuehl · Accepted solution]

Maybe like this:

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