Level 3: Aggregation Calculation Filtering

Version

Application Scenario

You can filter the result value of an aggregation calculation. For example, after working out the sales volume for each product, you only want to display the products that meet the condition.

The following figure shows the filtered data for products with a value of sales volume of more than 2000000.

The following figure shows the filtered data for products with a value of sales volume profit of more than 20%.

Function Description

You can drag a field (having been performed an aggregation calculation on) into Filter to filter the result value.

When dragging a regular indicator field into Filter, you need to select aggregation methods before filtering.

When dragging an aggregation function field into Filter, you can directly perform filtering.

When dragging the field Count into Filter, you need to select count dependence before filtering.

Note:

1. If you add the filtering condition Largest N/Smallest N to a field value, the filtering belongs to the fourth level of quick calculation filtering.

2. Detail tables do not support aggregation calculation filtering.

Click and download the sample data: Office Supplies Data.xlsx.

Create an analysis subject, upload the sample data, and drag the fields Product and Sales Volume into the analysis area to obtain the data of the sales volume for each product.

Filtering the Data of Products with a value of Sales Volume more than 2000000

You can filter the field Sales Volume in the analysis area through the following two entries.

Entry one: Drag the field Sales Volume into Filter, select Sum, and set the filtering condition to More than, Fixed Value, and 2000000 in sequence.

Entry two: Click the  icon next to the field in the analysis area (or click the  icon next to the table header) and select Filter from the drop-down list.

Filtering the Data of Products with a Value of Sales Volume Profit more than 20%

You can filter the aggregation function field Profit Rate.

1. Create a calculation field named Profit Rate and enter the formula: SUM_AGG(Profit)/SUM_AGG(Sales Volume).

2. Drag the aggregation function field Profit Rate into Filter. (The current dimension field is Product Name, therefore, the filtered data is the profit rate of each product.) Set the filtering condition of the field value to More than, Fixed Value, and 0.2 in sequence.

Filtering the Data of Products with a Value of Customer Number more than 200

1. Drag the field Count into Filter and select Customer. (The current dimension field is Product Name, therefore, the filtered data is the customer number of each product.)

2. Set the filtering condition of the field value to More than, Fixed Value, and 200 in sequence.

The following figure shows the effect.