Filter

Version

Application Scenario

For example, a data table contains product contract information of multiple years. If you only require the contract data of 2017, you need to filter and process the data before use.

Function Description

FineBI supports the data filter function, allowing you to filter and save data for subsequent analysis.

On the data editing page, you can add filter steps to perform complete and complex filter on the data or simply click table headers to perform simple filter. The following section introduces the methods.

Adding Filter Steps

Directly click Filter on the editing page or click the + icon and click Filter on the right to open the filter setting page.

For details about how to add filter steps, see section "Example."

Filter Through Table Headers

FineBI also allows you to directly filter data through certain field headers.

For example, the following figure demonstrates how to only view long-term contracts.

Sample data: Contract Fact Table.xlsx

The following section illustrates how to perform multiple conditions/formulas on a data table and display the filtered result with multiple conditions.

Adding Data

Sample data: Contract Fact Table (Part).xlsx

Download the sample data, create an analysis subject, add data, and go to the data editing page.

Adding Filter

To use filtered data, you can select fields and click Filter or click the + icon on the operation process bar and click Filter to add filter. 

Two types of filter conditions (Add Condition and Add Formula) can be chosen on the filter setting page.

Setting Filter Conditions for Text Fields

1. Select filter fields.

Click Add Condition (AND) and click Please Select Field. Then selectable filter fields are displayed, which are fields added in the current data table. The selectable fields in this example are fields that have been added during the field selection, including Contract Signing Time, Contract ID, Purchase Quantity, and Total Amount.

2. Set filter conditions.

For example, filter out the data in which Contract ID begins with 2a.

Select the text field Contract ID, select Begin With as the filter condition, and enter the text 2a to filter out the following content.

Explanation for text field filter conditions:

The optional filter conditions for text fields include In, Not In, Contain, Not Contain, Null, Not Null, Begin With, Not Begin With, End With, and Not End With.

• If you select In/Not In, you can select the field value in the current text field for filter. While for other filter conditions, you need to enter texts.

• If you select In/Not In, you can search for strings containing spaces or search for characters containing spaces through fuzzy search in the filter box.

• If you select Begin With/Not Begin With, you cannot enter the special character (}) in the case of real-time data (connecting to MySQL databases).

Setting Filter Conditions for Numeric Fields

Purpose: Filter out the data in which Contract ID begins with 2a or Total Amount surpasses the average.

Implementation idea: Set the OR relationship for two conditions.

Implementation steps: 1. Select filter fields. Click the ^ icon, click OR Condition, click Please Select Field, and select the numeric field Total Amount. 2. Set filter conditions. If you set the condition that Total Amount is more than the average, the system will automatically calculate the average of Total Amount, then filter out the data in which Total Amount is more than the average.

Explanation for numeric field filter conditions:

The optional filter conditions for numeric fields include Between, Not Between, Equal to, Not Equal to, More than, Less than, More than/Equal to, Less than/Equal to, Largest N, Smallest N, Null, and Not Null.

• If you select More than, Less than, More than/Equal to, or Less than/Equal to, you can select Fixed Value or Average. If you select Fixed Value, you need to manually enter a value. If you select Average, the system automatically calculates the numeric field's average and filters out the data based on conditions.

Setting Filter Conditions for Time Fields

Purpose: Filter out the data that satisfies one of the above filter conditions or in which Contract Signing Time is in the six years before the current time.

Implementation idea: Continue to add OR Condition and add a condition to dynamically filter out the data in which Contract Signing Time is in the six years before the current time.

Implementation steps: 1. Select filter fields. Click Add Condition (OR), click Please Select Field, and select the time field Contract Signing Time. 2. Set filter conditions. Select In and set that Contract Signing Time is between the Year Start and Year End of the year 6 years before the current time by clicking the Dynamic Time tab.

Explanation for Time Field Filter Conditions:

The optional filter conditions for numeric fields include In, Not In, Earlier than, Later than, Equal to, Not Equal to, Earliest N, Latest N, Null, and Not Null.

• Year-Month-Day or Dynamic Time can be set for the date. Year-Month-Day is a fixed date, while Dynamic Time can be set compared to the current time and can dynamically change with the current time during preview.

Adding Formula Filter

Purpose: Add a condition to filter out the data in which the contract unit price is more than one million.

Implementation steps: Set the condition as Total Amount / Purchase Quantity > 1000000 and click OK.

Note:

Fields in the formula need to be selected by clicking the field box and will be invalid if the fields are manually entered.

Effect Display

You have filtered out the data that satisfies one of the first three conditions and at the same time satisfies the condition that the contract unit price is more than one million.

Click Save and Update and click Exit and Preview.

Calculation Result Number

The data preview page only displays the first 5000 rows of the calculation result.

Actual Calculation Number

You can choose whether to use all data for calculation on the data editing page.

Manually Entered Filter Items

Sometimes, the selected filter fields cannot display all filter items due to the actual calculation number. In this case, you can manually enter filter items to add and select filter items.

For example, if you need to filter out the shop number 63021 which is not displayed in the list, you can manually enter 63021 to add and select it.

Then the filter item is added successfully to the filter condition. Click OK.

Null Calculation Result After Filter

If the calculation is performed using the first 100,000 rows of data, the filtered calculation result may be null. But data calculation and dashboard display are not affected. The first 5000 (can be manually modified) rows of all data calculation result can still be displayed on the analysis subject preview page.