Adding a Summary Column

Version

Application Scenario

You may need to perform some summary calculations without affecting the existing fields.

For example, to obtain the sales volume or the number of stores of each sales region, you can set Group Field to Region and perform some summary calculations on the sales volume or stores.

Function Description

Add a summary column based on the selected group field (the effect is similar to that of the fixed functions).

Different from calculations through Group Summary, calculations through Summary Column do not affect the existing fields.

Sample data: Store Sales Statistics.xls.

Calculate the sales volume, number of stores, and cumulative value of sales volume for each sales region.

Search for the Store Sales Statistics data set in My Analysis and click Edit. Click Field Settings and select required fields.

Click Summary Column.

Calculating the Sales Volume of Each Region

Set Group Field to Region and Summary Field to Sales Volume on the Summary Column setting page.

After you click OK, a new column is added to the data table to display the sum of sales volume of each region.

Calculating the Number of Stores in Each Region

Set Group Field to Region, Summary Field to Store Name, and Summary Method to Distinct Count.

After you click OK, a new column is added to the data table to display the number of stores in each region.

Calculating the Cumulative Value of Sales Volume over Time

Set Summary Field to Sales Volume and Summary Method to Cumulative Value.

Then you can select the sort field and sort order. In the following figure, the sales volume is accumulated in ascending order of Sales Date. If the sales dates are the same, the sales volume is accumulated in ascending order of Sales Volume.

After you click OK, a new column is added to the data table to display the cumulative value of sales volume over time.

Calculating the Group Cumulative Value

The previous section introduces how to calculate the cumulative value of all columns.

This section introduces how to calculate the group cumulative value. For example, calculate the cumulative sales volume of each store based on the sales date.

1. Sort the fields to facilitate the observation of cumulative effects. Sort the data table first by Store Name and sort the data table by Sales Date if the store names are the same.

2. Calculate the cumulative value of store sales volume (summarizing the sales volume grouped by Store Name) with sales volume accumulated in ascending order of Sales Date.

Click Summary Column and set Group Field to Store Name, Summary Field to Sales Volume, Summary Method to Cumulative Value, and Sort Field to Sales Date.

The result is shown in the following figure.