Pyramid Ranking

[Guest]

Hi,

I am not sure what this is called, but here is what i am trying to do. I
have a table with a column of salespeople names with there relative sales $
figures in the second column. I need to be able to calculate how many people
achieved the top 80% of sales, next 15% of sales and bottom 5% of sales.

To do this manually in excel i have been sorting salespeople and sales, by
sales, in decending order. Then multiplying the percentages by the total of
sales to get the weights and then counting the names. However this is time
consuming and i need to be able to do alot of different configurations. i.e
breaking the data down into regions and specific retail stores etc.

Does anyone know what i am on about?

Regards,

Ben

 

[Steve]

You can do this with with some queries.

For the top 80%, ceate a query based on your table and sort descending by
sales. Right click the query window and a dialog will open. Set the Top
property tp 80%.

For the next 15%, create an unmatched query that includes your table and the
first query to find the records in the table that were not included in the
first query. Again sort descending by sales. Right click the query window
and a dialog will open. Set the Top property tp 15%.

For the bottom 5%, ceate a query based on your table and sort ascending by
sales. Right click the query window and a dialog will open. Set the Top
property tp 5%.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Steve,

Thanks for the reply. However, i am having a little trouble finding what you
mean by the "top property". can you be more specific? i.e if i do as you say
and the menu appears, the first "top" property is SQL view.

Ben

 

[StopThisAdvertising]

You can do this with with some queries.

For the top 80%, ceate a query based on your table and sort descending by
sales. Right click the query window and a dialog will open. Set the Top
property tp 80%.

For the next 15%, create an unmatched query that includes your table and the
first query to find the records in the table that were not included in the
first query. Again sort descending by sales. Right click the query window
and a dialog will open. Set the Top property tp 15%.

For the bottom 5%, ceate a query based on your table and sort ascending by
sales. Right click the query window and a dialog will open. Set the Top
property tp 5%.

PC Datasheet

--
This is to inform 'newbees' here about PCD' Steve:
http://home.tiscali.nl/arracom/whoissteve.html
Until now 3550+ pageloads, 2325+ first-time visitors (these figures are rapidly increasing)

Why is this ???
Because Steve is the ONLY person here who continues to advertise in the groups.

It is not relevant whether he advertised in *this* particular post or not...
==> We want him to know that these groups are *not* his private hunting grounds!

For those who don't like too see all these messages:
==> Simply killfile 'StopThisAdvertising'.
Newbees will still see this warning-message.

ArnoR

 

[Guest]

Hi,

Thanks for the heads up, but i just want an answer to my question. "Steve's"
didn't really provide much help.

Ben

 

[John Spencer]

You will need three queries.
Assumptions:
-- Table has SalesPerson field
-- Table has SalesAmount

In a query
SELECT TOP 85% SalesPerson, SalesAmount
FROM YourTable
ORDER BY SalesAmount DESC

In the query grid
-- Add the two fields
-- RIght click in the grey area at the top of the query grid and select
properties
-- In the properties dialog set Top Values property to 80%
-- Sort by SalesAmount Descening

Bottom 5%
-- Change Top Values to 5%
-- Change sort to Ascending.

The other 15% is trickier and you can get it in a couple of ways. A problem
is that Access returns TIES when it is getting the Top values. So if all
your sales amounts were 100 then the bottom 5% would be all your
salespersons and the top 85% would be the same thing.

TO get the remaining 15% I would probably use a complex query using
sub-queries. I would suggest that you might want to use the unmatched query
wizard and a series of queries.
Using the Top 80% query and YourTable, build an unmatched query that gets
all the SalesPersons in the table that are NOT in the TOP 80%. Now use this
new query and the Bottom 5% query to get all the SalesPersons that are in
the new query and not in the Bottom 5%.




--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Gary Walter]

I am not sure what this is called, but here is what i am trying to do. I
have a table with a column of salespeople names with there relative sales
$
figures in the second column. I need to be able to calculate how many
people
achieved the top 80% of sales, next 15% of sales and bottom 5% of sales.

To do this manually in excel i have been sorting salespeople and sales, by
sales, in decending order. Then multiplying the percentages by the total
of
sales to get the weights and then counting the names. However this is time
consuming and i need to be able to do alot of different configurations.
i.e
breaking the data down into regions and specific retail stores etc.

Does anyone know what i am on about?

Hi Ben,

In addition to previous sage advice,
here may be an example that will help...

in NorthWind, a query that might come
close to your starting situation...

no big deal here...just a starting point to get
some (possibly similar) data...

qryProductSales

SELECT Products.ProductName, Sum([Order Details Extended].ExtendedPrice) AS
ProductSales
FROM Products INNER JOIN (Orders INNER JOIN [Order Details Extended] ON
Orders.OrderID = [Order Details Extended].OrderID) ON Products.ProductID =
[Order Details Extended].ProductID
WHERE (((Orders.OrderDate) Between #1/1/1997# And #12/31/1997#))
GROUP BY Products.ProductName;

so we have 2 fields -- a product and its sales over a period

now, here's what I think you might want to do...

qrySalesRanking

SELECT
Q1.ProductName,
Q1.ProductSales,
DCount("*","qryProductSales","[ProductSales]<=" & [ProductSales]) AS
SalesRank,
DCount("*","qryProductSales") AS SalesCount,
[SalesRank]/[SalesCount] AS SalesRankPerCent
FROM qryProductSales AS Q1
ORDER BY
Q1.ProductSales DESC;

then I think you want something like

SELECT
Switch([SalesRankPerCent]<=0.05,"Bottom 5%",
[SalesRankPerCent]<0.8,"Middle 15%",
True,"Top 80%") AS PerCentGrp,
Count(*) AS RankCnt
FROM qrySalesRanking AS Q2
GROUP BY
Switch([SalesRankPerCent]<=0.05,"Bottom 5%",
[SalesRankPerCent]<0.8,"Middle 15%",
True,"Top 80%") AS PerCentGrp;

If you have time you might try it
and see if not similar to what you
are trying to do...

I could be mistaken...

good luck,

gary

 

[Steve]

Take a look at John Spencer's response. He is telling you the same thing I
did.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Keith Wilby]

"Steve's"
didn't really provide much help.

No change there then.

 

[Guest]

Thankyou John,

That worked a treat. What i did is 2 queries as explained and then did an
unmatched query using the two previously created queries as sub queries
linked to a sub query that has all data. As a result this gives me 3 queries,
the result being top 80% sales, bottom 5% sales and the remaining 15%.

Ben

 

[Guest]

Hi again,

Actually, it is not working. it is just giving me 80% of the table, rather
than 80% of the actual $ sales. I have tried changing a few things, but for
example if i have 553 records, it filters out and gives me 442 = 80%, rather
than totaling the sales figure, and giving me the names that fall in that top
80%.

Ben

 

[John Spencer]

Then perhaps what you want is a query that calculates the totals by salesman
and then getting the top 80% of that

SELECT TOP 80% SalesPerson
, Sum(Sales) as Total Sales
FROM YourTable
GROUP BY SalesPerson
ORDER BY Sum(Sales) DESC

The easiest way to do all three of the queries you want is to build and save
a query that gets the totals and then use that in place of the table.

In the query grid
-- Add the two fields
-- Select View: Totals from the menu
-- Change GROUP BY to Sum under the Sales field
-- Save the query and close it

NOW use the query instead of the table when you build your other queries.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Gary Walter]

I am not sure what this is called, but here is what i am trying to do. I
have a table with a column of salespeople names with there relative sales
$
figures in the second column. I need to be able to calculate how many
people
achieved the top 80% of sales, next 15% of sales and bottom 5% of sales.

To do this manually in excel i have been sorting salespeople and sales, by
sales, in decending order. Then multiplying the percentages by the total
of
sales to get the weights and then counting the names. However this is time
consuming and i need to be able to do alot of different configurations.
i.e
breaking the data down into regions and specific retail stores etc.

Does anyone know what i am on about?

Hi Ben,

In addition to previous sage advice,
here may be an example that will help...

First. in NorthWind sample db, a query that might come
close to your starting situation...

no big deal here...just a starting point to get
some (possibly similar) data...

qryProductSales

SELECT Products.ProductName, Sum([Order Details Extended].ExtendedPrice) AS
ProductSales
FROM Products INNER JOIN (Orders INNER JOIN [Order Details Extended] ON
Orders.OrderID = [Order Details Extended].OrderID) ON Products.ProductID =
[Order Details Extended].ProductID
WHERE (((Orders.OrderDate) Between #1/1/1997# And #12/31/1997#))
GROUP BY Products.ProductName;

so we have 2 fields -- 77 products and their sales over a period


qryProductSales Product Name ProductSales
Alice Mutton $17,604.60
Aniseed Syrup $1,724.00
Boston Crab Meat $9,814.73
Camembert Pierrot $20,505.40
Carnarvon Tigers $15,950.00
Chai $4,887.00
Chang $7,038.55
Chartreuse verte $4,475.70
Chef Anton's Cajun Seasoning $5,214.88
Chef Anton's Gumbo Mix $373.62
Chocolade $1,282.01
Côte de Blaye $49,198.08
Escargots de Bourgogne $2,076.28
Filo Mix $2,124.15
Fløtemysost $8,438.74
Geitost $786.00
Genen Shouyu $1,474.82
Gnocchi di nonna Alice $32,604.00
Gorgonzola Telino $7,300.74
Grandma's Boysenberry Spread $2,500.00
Gravad lax $629.20
Guaraná Fantástica $1,630.12
Gudbrandsdalsost $13,062.60
Gula Malacca $6,737.93
Gumbär Gummibärchen $10,443.06
Gustaf's Knäckebröd $4,233.60
Ikura $9,935.50
Inlagd Sill $6,894.15
Ipoh Coffee $11,069.90
Jack's New England Clam Chowder $4,957.25
Konbu $812.94
Lakkalikööri $7,379.10
Laughing Lumberjack Lager $910.00
Longlife Tofu $1,000.50
Louisiana Fiery Hot Pepper Sauce $9,373.18
Louisiana Hot Spiced Okra $2,958.00
Manjimup Dried Apples $24,570.80
Mascarpone Fabioli $2,668.80
Maxilaku $3,128.60
Mishi Kobe Niku $6,935.50
Mozzarella di Giovanni $11,602.80
Nord-Ost Matjeshering $6,034.40
Northwoods Cranberry Sauce $4,260.00
NuNuCa Nuß-Nougat-Creme $1,692.60
Original Frankfurter grüne Soße $4,761.38
Outback Lager $5,468.40
Pâté chinois $7,832.88
Pavlova $8,663.40
Perth Pasties $10,661.93
Queso Cabrales $6,911.94
Queso Manchego La Pastora $8,335.30
Raclette Courdavault $35,775.30
Ravioli Angelo $2,156.70
Rhönbräu Klosterbier $4,485.54
Röd Kaviar $3,517.50
Røgede sild $3,355.87
Rössle Sauerkraut $13,948.68
Sasquatch Ale $2,107.00
Schoggi Schokolade $10,974.00
Scottish Longbreads $4,157.50
Singaporean Hokkien Fried Mee $5,408.20
Sir Rodney's Marmalade $7,314.30
Sir Rodney's Scones $5,273.00
Sirop d'érable $9,091.50
Spegesild $2,981.40
Steeleye Stout $5,274.90
Tarte au sucre $21,638.29
Teatime Chocolate Biscuits $2,986.75
Thüringer Rostbratwurst $34,755.92
Tofu $6,234.48
Tourtière $3,184.28
Tunnbröd $2,288.70
Uncle Bob's Organic Dried Pears $9,186.30
Valkoinen suklaa $2,173.44
Vegie-spread $6,899.25
Wimmers gute Semmelknödel $8,056.47
Zaanse koeken $2,930.75


now, here's what I think you might want to do...

qrySalesRanking

SELECT
Q1.ProductName,
Q1.ProductSales,
DCount("*","qryProductSales","[ProductSales]<=" & [ProductSales]) AS
SalesRank,
DCount("*","qryProductSales") AS SalesCount,
[SalesRank]/[SalesCount] AS SalesRankPerCent
FROM qryProductSales AS Q1
ORDER BY
Q1.ProductSales DESC;


qrySalesRanking Product Name ProductSales SalesRank SalesCount SalesRankPerCent
Côte de Blaye $49,198.08 77 77 1
Raclette Courdavault $35,775.30 76 77 0.987012987012987
Thüringer Rostbratwurst $34,755.92 75 77 0.974025974025974
Gnocchi di nonna Alice $32,604.00 74 77 0.961038961038961
Manjimup Dried Apples $24,570.80 73 77 0.948051948051948
Tarte au sucre $21,638.29 72 77 0.935064935064935
Camembert Pierrot $20,505.40 71 77 0.922077922077922
Alice Mutton $17,604.60 70 77 0.909090909090909
Carnarvon Tigers $15,950.00 69 77 0.896103896103896
Rössle Sauerkraut $13,948.68 68 77 0.883116883116883
Gudbrandsdalsost $13,062.60 67 77 0.87012987012987
Mozzarella di Giovanni $11,602.80 66 77 0.857142857142857
Ipoh Coffee $11,069.90 65 77 0.844155844155844
Schoggi Schokolade $10,974.00 64 77 0.831168831168831
Perth Pasties $10,661.93 63 77 0.818181818181818
Gumbär Gummibärchen $10,443.06 62 77 0.805194805194805
Ikura $9,935.50 61 77 0.792207792207792
Boston Crab Meat $9,814.73 60 77 0.779220779220779
Louisiana Fiery Hot Pepper Sauce $9,373.18 59 77 0.766233766233766
Uncle Bob's Organic Dried Pears $9,186.30 58 77 0.753246753246753
Sirop d'érable $9,091.50 57 77 0.74025974025974
Pavlova $8,663.40 56 77 0.727272727272727
Fløtemysost $8,438.74 55 77 0.714285714285714
Queso Manchego La Pastora $8,335.30 54 77 0.701298701298701
Wimmers gute Semmelknödel $8,056.47 53 77 0.688311688311688
Pâté chinois $7,832.88 52 77 0.675324675324675
Lakkalikööri $7,379.10 51 77 0.662337662337662
Sir Rodney's Marmalade $7,314.30 50 77 0.649350649350649
Gorgonzola Telino $7,300.74 49 77 0.636363636363636
Chang $7,038.55 48 77 0.623376623376623
Mishi Kobe Niku $6,935.50 47 77 0.61038961038961
Queso Cabrales $6,911.94 46 77 0.597402597402597
Vegie-spread $6,899.25 45 77 0.584415584415584
Inlagd Sill $6,894.15 44 77 0.571428571428571
Gula Malacca $6,737.93 43 77 0.558441558441558
Tofu $6,234.48 42 77 0.545454545454545
Nord-Ost Matjeshering $6,034.40 41 77 0.532467532467532
Outback Lager $5,468.40 40 77 0.519480519480519
Singaporean Hokkien Fried Mee $5,408.20 39 77 0.506493506493506
Steeleye Stout $5,274.90 38 77 0.493506493506494
Sir Rodney's Scones $5,273.00 37 77 0.480519480519481
Chef Anton's Cajun Seasoning $5,214.88 36 77 0.467532467532468
Jack's New England Clam Chowder $4,957.25 35 77 0.454545454545455
Chai $4,887.00 34 77 0.441558441558442
Original Frankfurter grüne Soße $4,761.38 33 77 0.428571428571429
Rhönbräu Klosterbier $4,485.54 32 77 0.415584415584416
Chartreuse verte $4,475.70 31 77 0.402597402597403
Northwoods Cranberry Sauce $4,260.00 30 77 0.38961038961039
Gustaf's Knäckebröd $4,233.60 29 77 0.376623376623377
Scottish Longbreads $4,157.50 28 77 0.363636363636364
Röd Kaviar $3,517.50 27 77 0.350649350649351
Røgede sild $3,355.87 26 77 0.337662337662338
Tourtière $3,184.28 25 77 0.324675324675325
Maxilaku $3,128.60 24 77 0.311688311688312
Teatime Chocolate Biscuits $2,986.75 23 77 0.298701298701299
Spegesild $2,981.40 22 77 0.285714285714286
Louisiana Hot Spiced Okra $2,958.00 21 77 0.272727272727273
Zaanse koeken $2,930.75 20 77 0.25974025974026
Mascarpone Fabioli $2,668.80 19 77 0.246753246753247
Grandma's Boysenberry Spread $2,500.00 18 77 0.233766233766234
Tunnbröd $2,288.70 17 77 0.220779220779221
Valkoinen suklaa $2,173.44 16 77 0.207792207792208
Ravioli Angelo $2,156.70 15 77 0.194805194805195
Filo Mix $2,124.15 14 77 0.181818181818182
Sasquatch Ale $2,107.00 13 77 0.168831168831169
Escargots de Bourgogne $2,076.28 12 77 0.155844155844156
Aniseed Syrup $1,724.00 11 77 0.142857142857143
NuNuCa Nuß-Nougat-Creme $1,692.60 10 77 0.12987012987013
Guaraná Fantástica $1,630.12 9 77 0.116883116883117
Genen Shouyu $1,474.82 8 77 0.103896103896104
Chocolade $1,282.01 7 77 9.09090909090909E-02
Longlife Tofu $1,000.50 6 77 7.79220779220779E-02
Laughing Lumberjack Lager $910.00 5 77 6.49350649350649E-02
Konbu $812.94 4 77 0.051948051948052
Geitost $786.00 3 77 0.038961038961039
Gravad lax $629.20 2 77 0.025974025974026
Chef Anton's Gumbo Mix $373.62 1 77 0.012987012987013


then I think you wanted something like

SELECT
Switch([SalesRankPerCent]<=0.05,"Bottom 5%",
[SalesRankPerCent]<0.8,"Middle 15%",
True,"Top 80%") AS PerCentGrp,
Count(*) AS RankCnt
FROM qrySalesRanking AS Q2
GROUP BY
Switch([SalesRankPerCent]<=0.05,"Bottom 5%",
[SalesRankPerCent]<0.8,"Middle 15%",
True,"Top 80%") AS PerCentGrp;

qryRangeCnt PerCentGrp RankCnt
Top 80% 16
Middle 15% 58
Bottom 5% 3


or, if you want all in one record

TRANSFORM Count(*) AS RankCnt
SELECT "NumProducts" AS PerCentGrp
FROM qrySalesRanking AS Q2
GROUP BY "NumProducts"
ORDER BY Switch([SalesRankPerCent]<=0.05,"Bottom 5%",[SalesRankPerCent]<0.8,"Middle 15%",True,"Top 80%") DESC
PIVOT Switch([SalesRankPerCent]<=0.05,"Bottom 5%",[SalesRankPerCent]<0.8,"Middle 15%",True,"Top 80%");

qryRangeCnt_xtab PerCentGrp Top 80% Middle 15% Bottom 5%
NumProducts 16 58 3


Of course I may have misunderstood...

good luck,

gary

 

[Gary Walter]

sorry...that last xtab did not need sort clause...

TRANSFORM Count(*) AS RankCnt
SELECT "NumProducts" AS PerCentGrp
FROM qrySalesRanking AS Q2
GROUP BY "NumProducts"
PIVOT Switch([SalesRankPerCent]<=0.05,"Bottom
5%",[SalesRankPerCent]<0.8,"Middle 15%",True,"Top 80%");