Filtering Level Introduction

  • Last update:July 08, 2024
  • Overview

    Since calculations are sequential, filtering also has levels. The calculation logic and effect vary according to the calculation position where you add filtering conditions.

    You can make great progress in the data analysis by learning about the filtering level.

    Reason for the Filter Level Existence

    If you change the filtering order for the same calculation, you will get different results.

    For example, you need to perform the following two steps for a group of data. First, filter data with sales equal to or greater than 100. Then calculate sales for each region. The final result displayed is the branches with sales equal to or greater than 100 and the total sales for each region.

    Now, change the order of filtering and calculation as follows. First, calculate the sales for each region. Then, filter the data with sales equal to or greater than 100. The final result displayed is the regions with total sales equal to or greater than 100 and their sales.

    There are four filtering levels in FineBI. The final calculation result varies according to the level at which filtering is perfomed. The following specifically describes the filtering levels in FineBI.

    Filtering Level in FineBI

    Calculations are sequential. The following table lists the logical calculation order in FineBI. In the practical analysis, all the following four calculations may not necessarily be used.

    Calculation Order
    Filtering Performed After the Calculation

    Edit the data table.

    Detail filtering: Perform a filtering on the processed data table.

    Add a DEF calculation field to the component.

    New column filtering: After a new column is output after the DEF function is used, perform filtering on the data table.

    Drag the field into the component to complete the aggregation calculation.

    Aggregation calculation filtering: Perform filtering on the result of the aggregation calculation.

    Use the quick calculation function to recalculate the aggregation value (secondary calculation).

    Quick calculation filtering: Perform filtering on the result of the quick calculation.

    The following streamlined diagram visually shows that the more to the left the filtering is, the more calculations it affects. For example:

    If detail filtering is performed, the calculation result of the DEF calculation field, component aggregation, and quick calculation will all be affected.

    If aggregation calculation filtering is performed, the result value of aggregation calculation will not be affected.


    Component Filtering Example

    Assume that you are the general manager of a shopping mall. You want to learn about the products that repeat customers prefer in 2021 to determine which products can attract and retain customers.

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

    Create an analysis subject, upload the sample data, and enter the Component tag page.

    Detail Filter

    Drag Contract Time into Filter and filter the data for 2021, as shown in the following figure.

    New Column Filtering

    1. Use the DEF function to calculate the quantity of orders per customer in 2021, as shown in the following figure.

    2. Filter the order data where the quantity of purchased orders is greater than or equal to 2. Such data is the order data of repeat customers.

    Component Calculation

    1. Create a calculation field Customer Quantity, as shown in the following figure.

    Formula
    DefinitionFunction Reference

    COUNTD_AGG(Customer)

    Deduplicate and count the customer IDs to obtain the quantities of customers.

    COUNTD_AGG

    2. Create a group table and drag the Product Name, Sales, and Customer Quantity fields into the table. View the quantities of repeat customers for each product and the corresponding sales.

    Aggregation Calculation Filtering

    There are two reasons for a high repurchase rate among users: low product prices and excellent product quality that is attractive to users.

    To find attractive products, you need to exclude the price reduction factor, that is, filter out products whose profit margins are too low.

    1. Create a calculation field Profit Margin, as shown in the following figure.

    2. Filter products with a profit margin greater than 0.1, as shown in the following figure.

    The following figure shows the post-filtering effect. According to the result, aggregation filtering does not affect the value results of aggregation calculations, for example: the values of Sales and Customer Quantity for Office Chair before and after the aggregation filtering are the same.

    Quick Calculation Filtering

    Rank the quantities of customers and filter the top 5 products.

    1. Use the quick calculation to rank Customer Quantity, as shown in the following figure.

    2. Filter the top five products, as shown in the following figure.

    Effect Display

    The final result displayed is the top five products by repurchase times (with the impact of low-price promotions excluded) in 2021.

    Summary

    The following figure shows the calculations and filtering you did throughout the entire process.


    附件列表


    主题: Creating a Visual Component
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    10s後關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy