topic Re: Top 15 sales in QlikView

Top 15 sales

Fri, 28 Dec 2012 16:22:50 GMT

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

pedromsouza — Fri, 28 Dec 2012 16:55:56 GMT

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

Re: Top 15 sales

Fri, 28 Dec 2012 16:59:33 GMT

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

Re: Top 15 sales

pedromsouza — Fri, 28 Dec 2012 17:01:21 GMT

Upgrade to Qlikview Desktop 11.

Re: Top 15 sales

Fri, 28 Dec 2012 17:04:02 GMT

Is there way to do this without an upgrade?

Re: Top 15 sales

montero91 — Fri, 28 Dec 2012 17:13:50 GMT

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

Re: Top 15 sales

chematos — Fri, 28 Dec 2012 17:17:03 GMT

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

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

Re: Top 15 sales

Fri, 28 Dec 2012 18:09:18 GMT

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

chematos — Fri, 28 Dec 2012 18:30:05 GMT

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

Fri, 28 Dec 2012 19:06:14 GMT

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

chematos — Fri, 28 Dec 2012 19:24:07 GMT

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

Fri, 28 Dec 2012 19:36:39 GMT

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

Re: Top 15 sales

chematos — Fri, 28 Dec 2012 19:42:20 GMT

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

Re: Top 15 sales

Fri, 28 Dec 2012 19:45:12 GMT

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