Filter Level Adjustment

Version

Application Scenario

Filtering and calculations have fixed orders by default at different filtering levels. Sometimes 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:

1. Filtering the data in 2021 (belonging to the first-level filtering)

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

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

Function Description

Indicator fields do not support the filter level adjustment.

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

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

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

Component Creation

1. Create an analysis subject, upload the sample data, and go to the component editing page.

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

The formula of the field Customer Number is COUNTD_AGG(Customer), which is to perform a deduplication count on the data of the field Customer.

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

Filtering Condition Addition

1. Drag the field Customer Purchase Order into Filter and set the filtering condition to more than or equal to a fixed value of 2.

2. Drag the field Signing Date into Filter to filter the data in 2021.

Filter Level Adjustment

From learning the two documents Detail Filtering and Filtering on New Column Output by the DEF Function, you will know:

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

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

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

If you want to track the repeat purchases of customers across the entire period, you need to calculate the new column output by the DEF function to obtain the result first and filter the result by Signing Date = 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 on the new column output by DEF).

You can adjust the filtering level of the condition Signing Date = 2021, which means calculating the data of the repeat purchases of customers across the entire period first and filtering the calculated result by Signing Date = 2021.

Filtering conditions at the same level do not have a specific order. The final result is the intersection of multiple filtering conditions.

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 field Signing Date and select New Column Filter from the drop-down list, as shown in the following figure.

Note:

Indicator fields are not supported to adjust the filtering level. 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.