Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

ankit777
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
MVP

Re: Some of Top 3 for each country

Maybe like this:

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

12 Replies
amit_saini
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

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

prajapatiamar38
Contributor II

Re: Some of Top 3 for each country

Hi try like this:

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

Hope this will help you

amit_saini
Honored Contributor III

Re: Some of Top 3 for each country

Check attachments

Thanks,
AS

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

ankit777
Valued Contributor

Re: Some of Top 3 for each country

none of these seem to work for me..

ankit777
Valued Contributor

Re: Some of Top 3 for each country

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

ankit777
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
MVP

Re: Some of Top 3 for each country

Maybe like this:

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

Community Browser