Statistics on Sales Values of the Top 3 Salespersons Distributed in Respective Product Categories

Problem

The table below is the sales record of a company within the given periods:



The table below is a list of salesperson.

The table below is a product list.

To evaluate the outstanding sales representatives, the company needs to rank the top 3 salesperson for total sales value and make statistics on the total sales value by respective product category achieved by each salesperson. Please find a solution for the request.

Tip

Rough train of thought: Group by salesperson in the Contract table to work out the total sales value of each salesperson. Select the top 3 salesperson by their total sales value. Group the record group by product category and work out the total sales value of each group, that is, the total sales value of each product category. Finally, sort out a result table sequence.

1. Firstly, update the Product and ActuaSale fields with the corresponding records in the Products table and Salesperson Table.
2. Group the contract table by ActualSale and create a new table sequence. Work out the total sales value of each group and reserve a field for holding the secondary grouping result.
3. List the sales value in descending order and take the first three ones who will be the top 3 Salespersons.
4. Group each record group again by product category. Work out the total sales value of each category and save the result sequence in the previously reserved field. By now, the table includes all required data.
5. Sort out the results and export them to a new table sequence. Change its structure to salespersons, product category, and sales value.

Code

The desired result is given in the cell A8.

Result