13 Replies Latest reply: Dec 28, 2012 2:45 PM by Zhenya Virina

# Top 15 sales

Hi -

I am trying to write an expression where in a bar chart i am only displaying top 15 Sales by region and sales people.

I tried ranking but i cant get the formula to work.

Dim 1 - Region

Dim 2 - Sales Person

Measure - Sales

Please help

Thank you!

• ###### Re: Top 15 sales

You can use the "Dimension Limits" tab on chart's properties.

• ###### Re: Top 15 sales

I dont see that option, where would i find it?

• ###### Re: Top 15 sales

Upgrade to Qlikview Desktop 11.

• ###### Re: Top 15 sales

Is there way to do this without an upgrade?

• ###### Re: Top 15 sales

if(rank(aggr(sum(Sales), Region, [Sales Person]))<=15,

aggr(sum(Sales), Region, [Sales Person]))

• ###### Re: Top 15 sales

This worked!

By any chance would you know how i can filter this expression to only display two regions and rank sales in order (Highest to lowest)?

thank you

• ###### Re: Top 15 sales

if(rank(aggr(sum({<Region = {'Region1','Region2'}>} Sales), Region, [Sales Person]))<=15,

aggr(sum({<Region = {'Region1','Region2'}>} Sales), Region, [Sales Person]))

You can configure orders in Order Sheet of object properties.

Could you mark the answer to close this discussion, please?

Thank you,

Regards.

• ###### Re: Top 15 sales

For some reason this formula does not work for me.

What would the best way to filter on the two reions? Create a separate dimension?

• ###### Re: Top 15 sales

use this :

if(rank(aggr(sum(Sales), Region, [Sales Person]))<=15,

aggr(sum(Sales), Region, [Sales Person]))

but ragion should be a calculated dimension: if( Region = 'RegionX' or Region = 'RegionY', Region)

• ###### Re: Top 15 sales

No i does not work - i can still see all regions. Is this all part of one string?

• ###### Re: Top 15 sales

Are you changing RegionX and RegionY for your regions?? Could you upload your qvw?

• ###### Re: Top 15 sales

I can't upload my file becuase of the sensative data. Yes here is what my expresion looks like

if(rank(aggr(sum("[Measures].[GL Amount]"), "[Legal Entity].[Company Code].[Company Code].[MEMBER_CAPTION]", "[Vendor].[Vendor Description].[Vendor Description].[MEMBER_CAPTION]"))<=15,

aggr(sum("[Measures].[GL Amount]"), "[Legal Entity].[Company Code].[Company Code].[MEMBER_CAPTION]", "[Vendor].[Vendor Description].[Vendor Description].[MEMBER_CAPTION]"),

if("[Legal Entity].[Company Code].[Company Code].[MEMBER_CAPTION]" = 'xxxx' or "[Legal Entity].[Company Code].[Company Code].[MEMBER_CAPTION]" = 'xxxx', "[Legal Entity].[Company Code].[Company Code].[MEMBER_CAPTION]"))

• ###### Re: Top 15 sales

In chart properties, go to the tab presentation incorporates a limit on the size, but  disables of the scroll