Overview
Version
| Version | Functional Change |
|---|---|
6.1 | / |
Application Scenario
The new column filtering is used to filter the results of calculations across rows.
For example, if you only want to analyze data for repeat customers, you can use the DEF function to calculate the number of times each customer has made a purchase Quantity of Orders Purchased by Customers. Execute in the filter: Set Quantity of Orders Purchased by Customers to be more than or equal to 2 to filter the order data of repeat customers.
Function Description
The new column filtering means performing a detail filtering on the calculation fields output by the DEF function.
What is the difference between the detail filtering and the new column filtering?
The new column filtering is a detail filtering performed after calculations across rows, so it will not affect the resulting values of calculations across rows.
The detail filtering is the filtering performed before calculations across rows, which may affect the resulting values of calculations across rows. For example, if Sales is filtered to be greater than 50, the final calculation result with the DEF function of City Sales for Beijing is 60.
Example: New Column Filtering Through the DEF Function
Assume that you are a data analyst for a shopping mall and want to analyze repeat customers by filtering their data.
You can download the sample data: Office Supplies Data.xlsx.
1. Create an analysis subject, upload the sample data, and go to the component editing page.
2. Create a calculation field Quantity of Orders Purchased by Customers, as shown in the following figure.
3 Drag the Quantity of Orders Purchased by Customers field to the filter, and set the filter condition to be greater than or equal to 2.
4. Create a calculation field Customer Quantity by counting the unique customer IDs. Drag Product Name, Customer Quantity, and Sales to the component and analyze the number of repeat customers and corresponding sales for each product.
The formula for calculating the number of customers is shown as follows: COUNTD_AGG(Customer)
As a result, you can filter and directly analyze the purchasing data of repeat customers by performing a new column filtering through the DEF function.
Let's recall how to achieve the number of users who repurchase a product and the corresponding sales before the new column filtering was introduced.
User Quantity: DEF(COUNTD_AGG(Customer),[Product Name],[Quantity of Orders Purchased by Customers≥2])
Sales: DEF(SUM_AGG(Sales),[Product Name],[Quantity of Orders Purchased by Customers≥2])
You need to write the above two functions to obtain two new calculation fields. After the comparison, you may feel the convenience brought by the addition of column filtering.
