Top N Clients Accounting for Half of the Total Sales in a Certain Year
l Problem
The table below is the historic sales contracts of an enterprise:
The table below is the Client table:
Among clients ranked by sales value in a certain year, the top N clients accounting for half of the total sales value are key accounts. Please list the key accounts of this enterprise in the year of 1998.
l Tip
General steps: Firstly, group the Contract table by Client, compute the total sales value achieved by each client and arrange the results in descending order, then work out the half value of the total sales value. Finally, scan this table,aggregating sales value cumulatively till reaching half of the total sales value. Then, these clients whose sales account for half of the total sales values are the key accounts.
1. In order to facilitate the client name retrieval, replace the Client field in the Contract table with the corresponding Client records.
2. Select out the Contract records in the 1998
3. Group the screened table by Client. Then, the sales contracts of each client will be grouped together and a new table sequence will be generated. Next sum up the amount of each group.
4. Sort this table sequence by sales amount in descending order
5. Compute the total sales amount of all clients, that is, half of the total annual sales value.
6. Traverse every record top-down with pselect function, and sum up the amounts cumulatively till the value reaches half of the total sum. Once traverse stops, you will get the number of key accounts- N.
7. Retrieve the names of top N clients, who are the key accounts of the year of 1998.
l Code
|
|
A |
|
|
1 |
=file("C:\\txt\\Contract.txt").import@t() |
Contract table |
|
2 |
=file("C:\\txt\\Client.txt").import@t() |
Client table |
|
3 |
>A2.keys(ID), A1.switch(Client,A2) |
Update the client field of contract table to client record |
|
4 |
=A1.select(year(SellDate)==1998) |
Screen out the sales record in 1998 |
|
5 |
=A4.group(Client;~.sum(Amount):Amount) |
Group by Client in the table, and sum up the amount of each group |
|
6 |
=A5.sort(-Amount) |
Sort by amount in descending order |
|
7 |
=A5.sum(Amount)/2 |
Half sales volume of this year |
|
8 |
=0 |
Temporary variable of aggregate amount |
|
9 |
=A6.pselect((A8=A8+Amount,A8>=A7)) |
Find the half position of the aggregate amount |
|
10 |
=A6(to(A9)).(Client.Name) |
Retrieve the names and they are the names of big client of this year |
l Result
