topic Re: Customer Concentration in QlikView

Customer Concentration

Anonymous — Fri, 12 Dec 2014 19:47:09 GMT

New to Qlik - appreciate all the help!

I'm looking to create a simple straight table that will provide a customer concentration based on top customers. I imagine that the formula includes some of the below, but I'm a little fuzzy on Aggr,and I haven't quite wrapped my head fully around it yet (hence my request for help here).

(Aggr(Rank(Sum([Extended Price]), [Customer Name]) ,=<10))

I've attached a picture for an idea of my end goal.

Thanks again!

Re: Customer Concentration

MK_QSL — Fri, 12 Dec 2014 22:46:25 GMT

Sorry but can you please provide some more information along with some sample data?

Re: Customer Concentration

Anonymous — Sun, 14 Dec 2014 18:23:19 GMT

Sorry - I cannot provide any sample data as it's all confidential information.

I want to sum the top 10 customers, and show them as a % of the total. Then do the same with Top 25, 50, & 100. This would take me 15 seconds in an excel pivot table.

Re: Customer Concentration

Anonymous — Sun, 14 Dec 2014 20:57:30 GMT

Man this is a good question!

Re: Customer Concentration

Anonymous — Mon, 15 Dec 2014 14:19:03 GMT

Thank you for providing absolutely nothing to the conversation. utkarshgarg.

Re: Customer Concentration

Anonymous — Mon, 15 Dec 2014 15:42:23 GMT

Apologies my friend but I tried to solve it but wasn't able to. Was just praising the question. Will try again and reply.

Re: Customer Concentration

Anonymous — Mon, 15 Dec 2014 15:46:06 GMT

No worries. The way you phrased it I had the impression you were making fun of me for not providing a clear picture.

It's really just a sum of the top 10 shown as a % of the total for the selected period.

Easy in excel, hard in Qlikview it seems.

Re: Customer Concentration

MK_QSL — Mon, 15 Dec 2014 15:49:43 GMT

Consider that you have Customer and Sales Data...

If you want to find out

SUM(Sales) of Top 10 Customer and their % respect to total Sales, use below expression in Text Box

=NUM(SUM(IF(Aggr(Rank(SUM(Sales),4),Customer)<=10, Aggr(SUM(Sales),Customer)))/SUM(TOTAL Sales),'0.00%')

For TOP 25.

=NUM(SUM(IF(Aggr(Rank(SUM(Sales),4),Customer)<=25, Aggr(SUM(Sales),Customer)))/SUM(TOTAL Sales),'0.00%')

Same as you can create for TOP 50, TOP 100 etc...

Re: Customer Concentration

Anonymous — Mon, 15 Dec 2014 16:00:34 GMT

That works! Thanks!

Side note, when calculated as is it is based on the selections in your app. If I have cleared all selections then it returns something else. I’ll dig into it to see further.

Dan Mercer

Financial Analyst

1111 Olive St

St. Louis, MO 63101

O: (314) 259-4123 | C: (618) 567-9615

dan.mercer@cosentry.com<mailt:dan.mercer@cosentry.com>

<http://www.cosentry.com/>

Re: Customer Concentration

MK_QSL — Mon, 15 Dec 2014 16:08:02 GMT

If you want to make expression which ignore Customer Selection, use as below

=NUM(SUM(IF(Aggr(Rank(SUM({<Customer = >}Sales),4),Customer)<=10, Aggr(SUM({<Customer = >}Sales),Customer)))/SUM(ALL Sales),'0.00%')

If you want to ignore Customer ans Country both

=NUM(SUM(IF(Aggr(Rank(SUM({<Customer =, Country = >}Sales),4),Customer)<=3, Aggr(SUM({<Customer =, Country = >}Sales),Customer)))/SUM(ALL Sales),'0.00%')