topic 80/20 formula in QlikView

80/20 formula

Wed, 24 Nov 2010 10:21:56 GMT

Hi, I am using this formula for my TOP 20 customers. (And which percentage they are in the total)

CONCERN_NAAM = client name and OMZET = SALESAMOUNT

However, I want to have the real 80/20, so I want to take the 20 % of the clients.

I have found these formulas, but I am not succeeding in placing them correctly

Top 80%: if(sum(Sale)> fractile(total aggr(sum(Sale),Name),.2),sum(Sale))
Bottom 20%: if(sum(Sale)<=fractile(total aggr(sum(Sale),Name),.2),sum(Sale))

Can somebody change the formula below, to reflect the top 20% of the CONCERN_NAAM ?

Thank you

='80/20
'&
num(
sum(total <Periodenummer,CONCERN_NAAM> aggr(
if(
rank(sum(OMZET))<=20
,
sum(OMZET))
, CONCERN_NAAM))

/
sum(total OMZET)
,'###.##0,00%', ',' , '.' )

80/20 formula

Wed, 24 Nov 2010 11:13:06 GMT

How about using the 'rank' function? For example, I have a chart that has a calculated dimension to rank my customers in terms of sales:

=If(AGGR(RANK(sum([MAS Net]) + sum([OTC Net]) + sum([WST Ext Net]) + sum([CSA Income])), Customer) <= vTop, AGGR(RANK(sum([MAS Net]) + sum([OTC Net]) + sum([WST Ext Net]) + sum([CSA Income])), Customer))

where vTop is a variable attached to a slider object so the user can define what top n customers is shown.

Hope this gives you some ideas.

Gordon

80/20 formula

Wed, 24 Nov 2010 11:24:20 GMT

Hi Gordon,

Thank you for taking the time to helpe me.

I am working on the slider, however I am still trying to show the 80/20 percentage.

Did you see my original formula? I am hoping somebody can tell mr how to replace the rank part into a fractile part...

80/20 formula

Wed, 24 Nov 2010 13:48:18 GMT

Hi.

I answered you in a different discussion: http://community.qlik.com/forums/t/26652.aspx

Check it out.

Regards,

Montal.