Customer Concentration

[Anonymous]

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!

[MK_QSL]

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

[Anonymous]

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. 

[Anonymous]

Man this is a good question! 

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[MK_QSL · Accepted solution]

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...

[Anonymous]

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/>

[MK_QSL]

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%')