topic number of customer who makes 80% of turnover in QlikView

number of customer who makes 80% of turnover

fredericmante — Wed, 27 May 2009 05:13:55 GMT

hi all,

i need your help
I would like to display in a text object, the number of customer who makes 80% of turnover.

Thanks in advance for your help

Fred

number of customer who makes 80% of turnover

Anonymous — Wed, 27 May 2009 08:15:20 GMT

It's a classic ParetoSelect - see APIGuide. There is example wich is 100% your solution.

number of customer who makes 80% of turnover

Wed, 27 May 2009 15:14:37 GMT

Hi Fred,

here are your macro:

'*************************************************************
'* Pareto 80_20
'*************************************************************
sub Pareto
set f = ActiveDocument.GetField ("Company")
f.ParetoSelect "Sum (Revenue)"
end sub

QV Version 9 will replace it by an ACTION (create a button and select an action: PARETO SELECT).

You also will have the option to choos the percentage (80/20 or 70/30 etc.).

Rainer

number of customer who makes 80% of turnover

johnw — Wed, 27 May 2009 23:39:01 GMT

ParetoSelect is great if you actually want to SELECT the top 80% and don't mind macros. But if all you want to see is a count, then it isn't really what you want. This comes up often enough that I eventually just sat down and worked out a solution. In the attached example, the expression calculates the number of weeks required to hit a given percentage of sales (specified in input variable Limit). I'm guessing that it could be easily modified to count customers instead. I can give it a shot if it ends up not being so simple. Here's the expression:

1 - sum(aggr(sum(if(Week<=N,Amount))/sum(total Amount)<Limit,N))

number of customer who makes 80% of turnover

yblake — Thu, 28 May 2009 07:56:30 GMT

Bonsoir Frédéric,

Ci-après un exemple de visiteurs médicaux.Cette expression indique le nombre de VM qui ont effectué 80 % des visites d'officines (dans mes données, 23 visiteurs sur 30).

= count(if(aggr(rangesum(top(count(Visites),1,rowno())) / rangesum(top(count(Visites),1,noofrows())),VM)<.8,VM,null()))

dans ton cas, j'essayerais :

= count(if(aggr(rangesum(top(sum(Sales),1,rowno())) / rangesum(top(sum(Sales),1,noofrows())),IdCustomer)<.8,IdCustomer,null()))

au plaisir

number of customer who makes 80% of turnover

johnw — Thu, 28 May 2009 23:54:24 GMT

Ah! Yes, that's a better approach than mine, as it doesn't require an extra field and seems more flexible in general. However, it doesn't include the customer that pushed you to or over 80%. And like my expression for Weeks, it just sorts by Customer ID, which likely isn't what you want here.

And unfortunately, I can't figure out how to tell the aggr() or top() functions a sort order. Aggr appears to always sort by the dimensions given.

Anyone else know how to add a sort by sum(Sales) to the expression?

Here's my simplification and fix for getting the customer that pushes you over 80%:

-sum(aggr(rangesum(top(sum(Sales),1,rowno()-1))/rangesum(top(sum(Sales),1,noofrows())),Customer)<Limit)

I've attached an example with that expression.

number of customer who makes 80% of turnover

fredericmante — Fri, 29 May 2009 00:11:13 GMT

Thank you for your responses. I will try your suggestions and you respond very quickly.

Fred

number of customer who makes 80% of turnover

Fri, 12 Jun 2009 05:43:55 GMT

John,

Thank for fir your example, help me solve my pareto analysis. Your comment about sorting, I solved the sort problem by having the dimension sort by, in your example, Sum(Amount)

HTH,

Stephen

number of customer who makes 80% of turnover

johnw — Fri, 12 Jun 2009 05:54:52 GMT

As long as you have a dimension to sort by, that should do the trick. But if you JUST want the final number using an aggr() function, I'm not sure how to do the equivalent of sorting the dimension. Still hoping someone comes up with an answer to that part.

Example has been modified to include your solution in the chart, and to demonstrate that we still have a problem with the simple expression.

number of customer who makes 80% of turnover

Wed, 22 Jul 2009 22:57:14 GMT

Solved it with a Macro (not my favorite way) but a much cleaner looking presentation to the user without having to show the straight table detail for the user to find the numbers. Look at "GetCell" in the API. It loops through the columns and rows in my straight table. So I use the code below to put the pareto count into a variable that I display in an input box. Below, when the counter reaches 17 it is at the column and row that has the total of the "1's" in the "pareto count".

sub ParetoCount

vCounter = 0

set table = ActiveDocument.GetSheetObject( "CH03" )

for RowIter = 0 to table.GetRowCount-1

for ColIter =0 to table.GetColumnCount-1

set cell = table.GetCell(RowIter,ColIter)

vCounter = vCounter + 1

if vCounter1 = 17 then

set v = ActiveDocument.Variables("vParetoCount")

v.SetContent cell.Text,true

exit sub

else

end if

end sub

number of customer who makes 80% of turnover

Tue, 10 Nov 2009 14:44:58 GMT

Please can you post an example containing the macro

number of customer who makes 80% of turnover

Thu, 24 Feb 2011 04:21:04 GMT

Hi John,

How will your file work if we add additional column says " quarter " in front of customer, and some sales figures contains negative values.

Will your sample still work ? because i'm applying it, and it doesn't seems to work.

Any advise? Thanks.

number of customer who makes 80% of turnover

johnw — Fri, 25 Feb 2011 21:25:59 GMT


nicksatch wrote: How will your file work if we add additional column says " quarter " in front of customer, and some sales figures contains negative values.
Will your sample still work ? because i'm applying it, and it doesn't seems to work.

I'm not sure what you're asking. The example I posted back in 2009 doesn't work even as is. You don't have to add a column or make values negative to make it fail. It already fails. As I said, "Still hoping someone comes up with an answer".

number of customer who makes 80% of turnover

Wed, 27 Apr 2011 14:19:37 GMT

Hello Yves,

I have the same need that Frédéric.
I tested your proposition into a QlikView Application and it works only if your data are sorted by an indicator. When the data are not sorted it doesn't work.

I need to calculate into a Text Object :

How many customers cover 50% of sales of the current year ?
What is the cumulated sales amount of these customers ?
What are these customers ?

If someone could help me, you're the welcome.

Regards

Philippe

number of customer who makes 80% of turnover

yblake — Sat, 02 Jul 2011 09:50:48 GMT

Hello,

In my sense, the text object is not the best place to get sorted information, your second chart is the right way.

You may howerver highlight some ranking information in text object, i.e. to send alerts.

In your sample, you can get information from the n-th customer (replace n by the ranking required) :

name : =Subfield(Concat(Customer,'-',-[Sales Amount]),'-',n)

amount : =sum({<Customer={$(=firstsortedvalue ( Customer, -[Sales Amount],n))}>} [Sales Amount])

Yves

number of customer who makes 80% of turnover

Thu, 11 Aug 2011 14:20:10 GMT

Hello Yves,

Thank you for your answer and the solutions you propose.
Now it doesn't solve my problem.
In my sample, if you choose 80%, I would like to calculate into a text object that 6 customers cover 80% of Total Sales Amount and this amount is 2 345 000 $.

I know that the best way is to use a graphic object but my customer want it in a text object, like a summary.

Philippe

number of customer who makes 80% of turnover

Mon, 24 Oct 2011 17:10:00 GMT

Hi John,

Have you had any luck getting the pareto bucket analysis to work?

Namely, have you or someone else identified a way to sort within a chart (within an aggr or similar) to show the top selling customers, for example?

Thanks!

Tyler

Re: number of customer who makes 80% of turnover

rchasser — Thu, 03 Nov 2011 17:14:31 GMT

Hello,

I think i have a clue to fix it.

I used the Philippe file

First, i've build a graphic that monitor the same result whithout sorting the dimension ... thanks to a valuelist.

The problem is when i try to monitor the result in a text object it doesn't work. It seems that it is not possible to use a valuelist with an Aggr ...

If someone have an idea ?

Thanks

Romain

number of customer who makes 80% of turnover

johnw — Sat, 05 Nov 2011 00:30:09 GMT

tylerwaterfall wrote:
Hi John,
Have you had any luck getting the pareto bucket analysis to work?
Namely, have you or someone else identified a way to sort within a chart (within an aggr or similar) to show the top selling customers, for example?

Nope, I never got it working. I never tried very hard either, though.

number of customer who makes 80% of turnover

Sun, 06 Nov 2011 07:48:30 GMT

Good discussion !!!!!!!

Bhushan N