Clustering sales compared to total

[Guest]

Okay, this one is quite a nasty little setup. I need to separate items into
sales clusters that are defined by ranking all the items sales and
determining which ones compose the top 70% of total sales, the next 20, and
the last 10. So in pseudo code terms you need to rank the items from highest
selling to lowest in dollars, calculate the total sales, generate a running
sum, and identify when that running sum reaches 70% of the total sales
dollars.

The data setup is pretty straight forward with each item listed and it's
sales to date for the current month.

I have been able to generate a running sum in a query, but I am still at a
loss of a way to figure out where the top 70% falls. I used a Dsum to
generate the running sum and they only other idea I had was to use a criteria
versus this running sum that takes the Dsum of the entire data set and
multiplies by .70. This seems like it will work technically but when I
attempted to run it the query just hanged for a good 10 mins with no movement
on the progress meter.

The other big problem is that this needs to be dynamically linked to excel
to populate spreadsheets so I am limited in using make tables and so forth.

Anyone got any ideas or suggestions?

 

[Michel Walsh]

Hi,



SELECT a.ItemID
FROM myTable As a LEFT JOIN myTable As b
ON a.Sales < b.Sales
GROUP BY a.ItemID
HAVING SUM(Nz(b.Sales,0)) <= 0.7*(SELECT SUM(Sales) FROM MyTable)





where the 0.7 can be a parameter.

An ItemID will be kept if the sum of all the sales better than its own
(excluding its own sale) makes less the 70% of the total.

If there are many items at the 'breaking point", they will all be included,
making so more than 70%, in that case.

If the n best items make 69.9995 % of the total, then, including the n+1
best one will, very likely, make more than exactly 70% too. Using an inner
join rather than the proposed outer join would make a total LESS than
exactly 70%.

As far as updateability is concerned, that query is NOT updateable.


Hoping it may help,
Vanderghast, Access MVP