80-20 Graph Display in real time

[#1]

Hi Friends,

I'm working in QlikView Professional Layout. I'm generating report for Realtime data. I need to display the 80-20 value in the chart. At present i displayed the 80-20 with the help of "Presentation Tab, present in the chart properties" and checking the "max visible number(1-100) ".

But i would like to know, any other steps to handle this type of situation.

Could you please help me to out of this problem.

Regards,

R.Srinivasan

[#2]

at present i got a code from my support,

Here it is

Count ( if ( Aggr ( RangeSum( Top( Sum ( [Amount] ),1,rowno () )) / RangeSum( Top ( Sum ( TOTAL [Amount] ),1,rowno() )),Sum ([Amount])<0.8,Sum([Amount]),null() ) )

Could you help me. It's Urgent.

[johnw]

Well, you have unbalanced parentheses and don't appear to be aggregating BY anything, so the expression is pretty much junk. But if I'm reading it correctly, you want a chart of, for instance, the sales of only the customers making up the top 80% of sales? I think combine a sort of customers by sum(Sales) descending with this expression:

if(rangesum(top(sum(Sales),1,rowno()-1))/sum(total Sales)<0.8,sum(Sales))

Or do you want a count of the NUMBER of customers that make up the top 80% of sales? That I don't know how to do. I haven't had the need myself, but it DOES keep coming up on the forum, so I'd love to see a solution. The problem is that I believe you have to aggr() the data, and the aggr() function doesn't seem to allow you to specify a sort order. Therefore the customers in the internal chart are simply in I believe alphabetical order, which is totally not what you want.

[#4]

At present i have made this using the presentation tab under the chart properties. i.e, checking the option "Max Visible Number(1-100), so if i want to show the top customer i used to have some value in the visible number as "5 or 10 or even 15" based on the sum(amount).

I can manually do this, but in real-time i need to have some function which generate a number or count the customer who made 80%. So when i insert this function in the max visible number, i can have the 80-20 chart. I have sorted the customer according to the value, so i get highest at the top and lowest at bottom.

I have also attached a excel sheet for your reference. I think now you can understand, what i need.

[johnw]

Hmmm, yeah, sorry. Not sure how to do that. The problem is the one I mentioned - the aggr() doesn't allow you to sort by the value in descending order. Instead, it just sorts by the name in ascending order, which isn't at all what you need to get the correct count.

I really hope someone can solve it, though. Variations of this question are frequently asked, and while SOME variations have had solutions, many haven't, and I've seen no solution for this CLASS of problems. That's annoying since this is such a common, basic chart.

[hectorgarcia]

have you tried this? I found i long time ago

[hectorgarcia]

or this

[johnw]

It seems like most people asking the 80-20 questions don't actually want to SELECT the top 80%. They just want to SEE the top 80%, or get a count of the number of customers that make up the top 80%, or compare the top 80% to the remaining 20%, and so on. So while ParetoSelect may solve some problems, it doesn't seem to solve most of them. Still, it's a good tool for people to be aware of.

[#9]

Hi Garcia,

Thanks for you reply and the solution. I have already got this answer, but now i need to do in dynamic table. I have posted a sample in this page (http://community.qlik.com/forums/p/21510/85345.aspx#85345). Could you help me to find the solution.

[hectorgarcia]

try this one is working for me , specially with large set of data