topic Re: 10% of the customers with highest sale amount in QlikView

10% of the customers with highest sale amount

qlikconsultant — Fri, 20 Feb 2015 09:27:28 GMT

Hi,

I’m looking for a way to show 10% of the customers with highest sale amount?

I tried it with dimensions limit but it gets me only the customers with 10 % of the sale amount.

Any suggestions?

Re: 10% of the customers with highest sale amount

datanibbler — Fri, 20 Feb 2015 09:35:51 GMT

Hi Markus,

I think on the GUI you have the option, in the dialog of pretty much any chart, to limit the displayed values. I have never used that, so I cannot advise you further on that, but it should be possible.

HTH

Best regards,

DataNibbler

Re: 10% of the customers with highest sale amount

qlikconsultant — Fri, 20 Feb 2015 09:59:31 GMT

This Option is called "Dimensions Limit" and how I said it delivers not the desired result.

Re: 10% of the customers with highest sale amount

MK_QSL — Fri, 20 Feb 2015 10:39:36 GMT

Can you please elaborate little more on your requirement?

if possible, provide one small example... Would like to help you on this.

Re: 10% of the customers with highest sale amount

qlikconsultant — Fri, 20 Feb 2015 11:53:56 GMT

Ok here is an example:

Basic data:

Customer number	Sales €	Orders
1.	€ 20,00	1
2.	€ 30,00	3
3.	€ 40,00	4
4.	€ 10,00	2
5.	€ 2,00	1
6.	€ 20,00	10
7.	€ 15,00	3
8.	€ 20,00	4
9.	€ 10,00	5
10.	€ 90,00	6
11.	€ 180,00	20
12.	€ 30,00	10
13.	€ 20,00	2
14.	€ 20,00	1
15.	€ 50,00	5
16.	€ 200,00	10
17.	€ 10,00	20
18.	€ 80,00	8
19.	€ 10,00	1
20.	€ 20,00	2

Goal 10% of the the customers with the highest sale:

Customer number	Sales €	Orders
11.	€ 180,00	20
16.	€ 200,00	10

Re: 10% of the customers with highest sale amount

rustyfishbones — Fri, 20 Feb 2015 12:47:17 GMT

You could try for just the top 2

IF(AGGR(RANK(Sum(Sales)),[Customer number])<=2,[Customer number])

However I believe you could more customers and therefore this list needs to dynamically change based on the Customers.

I will take a further look

Regards

Alan

Re: 10% of the customers with highest sale amount

MK_QSL — Fri, 20 Feb 2015 12:48:09 GMT

10% of customers with highest price... What is the criteria for comparing !!...

Re: 10% of the customers with highest sale amount

jpenuliar — Fri, 20 Feb 2015 12:50:19 GMT

Calculated Dimension :

=Aggr(if(Rank(Sum(Sales_€))<= $(vNumCustomer),Customer_number),Customer_number)

Re: 10% of the customers with highest sale amount

jpenuliar — Fri, 20 Feb 2015 12:51:04 GMT

with variable vNumCustomer:

=(Count(Customer_number)*0.1)

Re: 10% of the customers with highest sale amount

daveamz — Fri, 20 Feb 2015 12:52:58 GMT

Hi Markus,

You can use Dimension Limit with this settings:

Show only = Largest xxx values where xxx can be calculated dynamically like count(customers) * 10%.

Best regards,

David

Re: 10% of the customers with highest sale amount

rustyfishbones — Fri, 20 Feb 2015 13:03:46 GMT

Create a Calculated Dimension with

Ensure to Suppress 0 Values

And your Expression will be Sum(Sales)