# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Valued Contributor

## 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?

1 Solution

Accepted Solutions
MVP

## Re: Some of Top 3 for each country

Maybe like this:

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

12 Replies
Honored Contributor III

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

MVP

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

Contributor II

## Re: Some of Top 3 for each country

Hi try like this:

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

Honored Contributor III

## Re: Some of Top 3 for each country

Check attachments

Thanks,
AS

MVP

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

Valued Contributor

## Re: Some of Top 3 for each country

none of these seem to work for me..

Valued Contributor

## Re: Some of Top 3 for each country

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

Valued Contributor

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

MVP

## Re: Some of Top 3 for each country

Maybe like this:

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