Level 2: Filtering Dimensions Depending on Indicators

Version

Application Scenario

The function is to filter dimension fields using indicator fields as the filtering condition to filter the detailed data that meets the condition. /

For example, you can filter the data of customers with the value of the field Sales Volume more than 100000 to quickly calculate the data of the number of high-spending customers for each product and their corresponding sales volume.

You can filter the data of customers with the value of the field Order Number equal to or more than 2 to quickly calculate the data of the number of repeat customers for each product and their corresponding sales volume.

Function Description

Filtering by the group to which the dimension fields belong is detail-level filtering, which takes effect at the second level (column addition filtering) due to its cross-row calculations like the DEF functions.

The scope of the dependent indicators used as conditions in dimension filtering:

The dimension fields can only be filtered depending on the indicator fields already dragged into the analysis area and not performed quick calculations yet.

The detail table component does support this filtering.

Note:

If multiple filtering conditions with "and/or" relationships (including conditions on the dimension field itself and the dependent indicator field) are added to a dimension field, the filter will take effect at the second level.

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

1. Create an analysis subject and upload the sample data. Create a calculation field named Customer Number, as shown in the following figure.

The formula COUNTD_AGG(Customer) is to perform a deduplication count on the value of the field Customer to obtain the number of customers.

2. Drag the fields Product, Customer Number, and Sales Volume into the analysis area to calculate the number of customers and sales volume for each product, as shown in the following figure.

You only need to analyze the data of the products with a value of the field Customer Number more than 200. Therefore, you can filter the detailed data of products that meet the condition.

You can filter the dimension fields in the analysis area through the following two entries.

Entry one: Click the  icon next to the field Product Name, select Filter from the drop-down list, delete the original filtering condition, click the Add Condition button, select the field Customer Number, and set Customer Number to More than, Fixed Value, and 200 in sequence.

Entry two: Drag the field Product Name into Filter, delete the original filtering condition, click the Add Condition button, select the field Customer Number, and set Customer Number to More than, Fixed Value, and 200 in sequence.

You only need to perform a component calculation on data of customers with the value of the field Sales Volume more than 100000. Therefore, you can filter the detailed information of customers that meet the condition.

Drag the field Customer into Filter, delete the original condition, click the Add Condition button, select the field Sales Volume, and set Sales Volume to More than, Fixed Value, and 100000 in sequence, as shown in the following figure.

The following figure shows the calculation result of the component after filtering.

You only need to perform a component calculation on data of customers with the value of the field Order Number more than 2. Therefore, you can filter the detailed information of customers that meet the condition.

1. Add a calculation field named Order Number, as shown in the following figure.

The formula COUNTD_AGG(Order Code) is to perform a deduplication count on the value of the field Order Code to obtain the number of orders.

2. Since the dimension fields can only be filtered depending on the indicator fields already dragged into the analysis area, if you want to filter the number of customer orders, you need to drag the field Order Number into the analysis area, as shown in the following figure.

3. Drag the field Customer into Filter, delete the original condition, click the Add Condition button, select the field Order Number, and set Order Number to More than/Equal to, Fixed Value, and 2 in sequence, as shown in the following figure.

The following figure shows the calculation result of the component after filtering.

You can select Filter by Group to Which Dimension Belongs when the dimension fields already dragged into the analysis area are filtered depending on the indicator fields.

Drag the fields Signing Date, Product Name, and Region into Dimension and Sales Volume into Indicator, click the  icon next to the field Signing Date, select Year from the drop-down list to calculate and obtain the annual sales volume of different products in different regions.

Adding a filtering condition to the field Product Name: Click the Add Condition button, select the field Sales Volume, and set Sales Volume to More than, Fixed Value, and 200000 in sequence. The following content shows the difference between selecting and deselecting Filter by Group to Which Dimension Belongs.

Selected

The value of the field Sales Volume is grouped by Signing Date and Product Name and summed to obtain data of the annual sales volume of each product, which is then filtered to obtain data with a value of more than 200000.

The following figure shows the calculation result of the data after filtering, namely the data of products with an annual sales volume of more than 200000.

Deselected

The value of the field Sales Volume is grouped by Product Name and calculated to obtain data of the sales volume of each product, which is then filtered to obtain data with a value of more than 200000.

Only the value of the total sales volume of Notebook in the field Product Name is less than 200000. Therefore, the data of Notebook is not included in the component calculation.