Top 3 Clients by Order Value

Compute the top 3 clients by order value according to the detailed order data. The basic train of thought is to group the orders by clients firstly, secondly sum up the total value of orders placed by each client, thirdly sort the order by the summary value, and lastly filter out the top 3 groups according to the serial numbers. In this case, the summary value sorting and filtering by the serial number is the difficult point. However, with the overall sheet design module and sorted computational module of esCalc, this point can be solved easily.

1. Get the data from the order sheet through the wizard

2. Group by client. Right-click on any cell outside the header of column B, select Quick operation->group, as shown in the below figure:

The grouping result is shown below:

3. Calculate the summary value of each client. In any cell of the summary zone, enter the summation formula, for example, enter {D3}.sum() in D2, which indicates the sum of all homo-cells of D3. Formulas will be copied and pasted to the homocells of D2 (i.e. D7, D10, and other cells) automatically, as shown in the below figure:

4. Sort by the summary value. Right-click on the cell where the summary value is located (any cell of D2, D7 or D10). Right click and select Quick operation->Desc, as shown in the below figure:

The sorting result is shown below:

5. Filter by the serial number. In the Summarizing Area (for example the cell B2), right click and select Operation->Filter, as shown in below figure:

In the dialogue, input the formula “#<=3", and # represents the serial number in this case. Because it is summary zone, the # represents "serial number of each member in the group", as shown in the below figure:

The filtered result of the computational goal, as shown in the below figure:

6. To make it more convenient for viewing, you can also click the grouping level 1 on the top left corner, as shown in the below figure: