Dimension Filter Level Adjustment

Target user: advanced filter users
You have learned the advanced filtering levels before studying this document, which lays the foundation for you to understand filtering level adjustments.

Version

Application Scenario

Filtering and calculations follow fixed orders by default at different filtering levels. In some cases, you may want to adjust the filtering order. For example, you want to view the order details in 2021 of customers who made repeat purchases over the entire period. In this case, you need to perform two filtering operations:

• 1. Filter the data in 2021 (at the first-level filtering).

• 2. Use the DEF function to calculate data of customers with at      least 2 purchases (at the second-level filtering).

The system performs the first filtering by default, but this will cause the data calculated by the DEF function to be the number of repeat purchases of customers in 2021 rather than over the entire period. If you do not want the first filtering operation to affect your DEF calculation, you can use the function of the filtering level adjustment.

Function Description

You can only adjust the filtering levels of the dimension.

Background: You want to view the order details in 2021 of customers who made repeat purchases over the entire period.

You can download the sample data: Office Supplies Data.xlsx.

Component Creation

1. Create an analysis subject, upload the sample data, and enter the component editing page, as shown in the following figure. 

2. Create a calculation field named Customer Number and drag Product Name, Customer Number, and Sales into the component to analyze the number of returning customers and corresponding sales for each product.

The formula of the field Customer Number is COUNTD_AGG(Customer), which returns the distinct count of the Customer field.

3. Create a calculation field named Customer Order Count, as shown in the following figure.

Filtering Condition Adding

1. Drag the Customer Order Count filed into Filter and set the filtering condition to the value of this field greater than or equal to the fixed value 2, as shown in the following figure. 

2. Drag the Contract Time field into Filter to filter the data in 2021, as shown in the following figure. 

Filtering Level Adjustment

After learning Level 1: Detail Filtering and Level 2: New Column Filtering Through the DEF Function, you will know:

• Contract Time = 2021: It refers to the filtering applied to the dimension itself and belongs to the first-level filtering (detail filtering) by default.

• Customer Order Count ≥ 2: It refers to the filtering applied to the new column output by the DEF function and belongs to the second-level filtering (filtering the data in the new column output by the DEF function) by default.

The following figure shows the order in which these two filtering conditions take effect for calculations, but the filtered results do not meet the requirements.

To count the repeat purchases of customers over the entire period, you need to calculate the new column output by the DEF function to obtain the result first and filter the result using the condition Contract Time = 2021.

The filtering that takes effect after the calculations performed on the new column output by the DEF function and before the component aggregation calculations belongs to the second-level filtering (filtering the data in the new column output by the DEF function).

You can adjust the filtering level of the condition Contract Time = 2021, which means calculating the data of the repeat purchases of customers first and filtering the calculated result using the condition Contract Time = 2021, as shown in the following figure. 

At the same level, the filtering conditions have no specific order and are in an AND relationship.

1. Click the  icon and select Adjust Dimension Filter Level from the drop-down list, as shown in the following figure.

2. Click the  icon next to the Contract Time field and select New Column Filter from the drop-down list, as shown in the following figure.

Note:

Filtering level adjustments for indicator fields are not supported. Therefore, the Adjust Dimension Filter Level page only displays dimension fields.

The following figure shows the calculation result of the component after adjustment, namely the purchase details in 2021 of the customers who made repeat purchases over the entire period.

You can click the  icon and select Auto from the drop-down list on the Adjust Dimension Filter Level page, as shown in the following figure.

The following table shows the corresponding adjustment levels according to various dimension filtering conditions.