topic Re: Filtering top 5 selling countries on a map chart in QlikView

Filtering top 5 selling countries on a map chart

Tue, 13 Jan 2015 14:50:15 GMT

Hi,

Is there a way to filter top 5 selling countries on a map chart?

I have Country (country_name) as a point layer and used Sales (sales_amount) in the following expression:

Aggr(Max(sales_amount), country_name)

It's seems that the expression works, but how do I get the top 5 Countries? I tried using 'Rank' in the expression but it only returned 1 Country:

Aggr(Rank(Max(sales_amount),country_name),<=5)

Best regards,

Linas

Re: Filtering top 5 selling countries on a map chart

alexandros17 — Tue, 13 Jan 2015 14:53:45 GMT

try with

=IF(Aggr(Rank(SUM(sales_amount),4),country_name)<=5,country_name)

Re: Filtering top 5 selling countries on a map chart

Tue, 13 Jan 2015 15:06:29 GMT

Hi Alessandro,

Thanks for a quick response.

2 things here:

1) The chart doesn't display anything if an option 'Use size' is selected.

2) Shows all countries, if de-selected.

By the way, this refers to QlikSense.

Thanks again,

Best regards,

Linas

Re: Filtering top 5 selling countries on a map chart

Tue, 13 Jan 2015 15:09:44 GMT

why don't you go to Dimension Limits in the chart properties and there specify the top 5 , leave the measure expression without the rank

sum(sales_amount)

dimension country

chart properties top 5 ,

let QlikView do its job

Re: Filtering top 5 selling countries on a map chart

Tue, 13 Jan 2015 15:11:22 GMT

Hi Mario,

That option doesn't exist on Map chart.

Many thanks,

Linas

Re: Filtering top 5 selling countries on a map chart

Tue, 13 Jan 2015 15:12:13 GMT

By the way, it's QlikSense

Re: Filtering top 5 selling countries on a map chart

Tue, 13 Jan 2015 15:14:24 GMT

QlikSense 🙂

Can you try this in the expression?.

IF( Aggr(Max(sales_amount), country_name) <= 5 ,

SUM(SALES_AMOUNT))

Re: Filtering top 5 selling countries on a map chart

Tue, 13 Jan 2015 15:15:40 GMT

sorry, it is

IF( Aggr(RANK(sales_amount), country_name) <= 5 ,

SUM(SALES_AMOUNT))

Re: Filtering top 5 selling countries on a map chart

Tue, 13 Jan 2015 15:22:32 GMT

I have a copy now of Qliksense on hand, I typed the expression

SUM ( IF( Aggr(RANK(SUM(sales_amount)), country_name) <= 5 ,

sales_amount

)

you can even return the country names instead

=concat ( IF( Aggr(RANK(SUM(sales_amount)), country_name) <= 5 ,

country_name

)

Re: Filtering top 5 selling countries on a map chart

Tue, 13 Jan 2015 15:23:45 GMT

Thanks Mario, both return only 1 (top) selling country.

Re: Filtering top 5 selling countries on a map chart

Tue, 13 Jan 2015 15:30:39 GMT

Funny, this shows all the Countries, but top 4 have large circles. Changed from '<= 5' to '<= 6' and can see top 5 selling countries with large (same size) circles + the rest in tiny little circles.

It's not exactly what I have been looking for, but sufficient enough!

Thanks Mario!

Re: Filtering top 5 selling countries on a map chart

Tue, 13 Jan 2015 15:32:26 GMT

ok. don't forget to close the thread and mark an answer. so that others can search the forum whenever they come to the same scenario.

Re: Filtering top 5 selling countries on a map chart

Tue, 13 Jan 2015 15:33:08 GMT

Strangely, the rest (not top 5) all show same Sales amount... must be a bug..

Re: Filtering top 5 selling countries on a map chart

Tue, 13 Jan 2015 15:36:10 GMT

can you send an image of your expression and result ?

Re: Filtering top 5 selling countries on a map chart

Wed, 14 Jan 2015 08:27:31 GMT

Hi Mario,

Can you send me your email, as for confidentiality reasons I don't want to post any images on Qlik community page.

Many thanks,

Linas