Basic functions of filter

1.1 Version

1.2 Application scenario

For example, the original data table saved in the package contains the fields "purchase quantity, amount quantity, contract signing time, product type", where "contract signing time" contains all the contract time in history, and the salesman only needs to use the data in 2017 when using it, then the original table needs to be filtered and processed before use.

1.3 Function introduction

FineBI provides a data filter function, which allows business people to add a new data table directly on top of the original one to filter and save the data for subsequent analysis.

2. Example

Example of multiple conditional/formula filtering on a data table at the same time, showing the results of filtering under multiple conditions.

2.1 Add dataset

1）Go to Data Preparation > Self-Service Dataset and click Add Table > Self-Service Dataset, as shown in the following figure.

2）Enter the self-service data set configuration interface, select the required fields "Product, Contract amount, purchase quantity, contract signing time" from the data table of the package, and the selected fields will be displayed in the data preview interface on the right, as shown in the following figure.

2.2 Add condition filter

1）Because the original data needs to be filtered and then used, after selecting the fields, click "+" in the leftmost operation flow column and select Filter, as shown in the following figure.

2）When you enter the filter setting interface, there are two kinds of filter conditions you can choose: "Add Condition" and "Add Formula".

2.2.1 Set text field filter

Click "Add conditions (and) > Please select field", which shows the optional conditions filter fields when adding conditions, and the optional fields are the fields that have been selected in the current self-service dataset, for example, the optional fields here are the "Product, contract amount, purchase quantity, contract signing time" fields that were added in the field selection stage, as shown in the figure below.

Select the field type as "Text Field", set the filter condition as "beginning is", enter the text "1", and filter out the following content, as shown below.

Note: If you choose the filter condition of begin with/not begin with here, the special character "}" is not supported in the case of real-time data (connected to MySQL database).

The optional conditions for the text field filter are「in、not in、contain、not contain、is empty、is not empty、begin with、not begin with、end with、not end with」。

Note: The "in/not in" condition allows you to select the field value in the current text field as the condition, while the rest of the filtering conditions require text input to filter.

When the "in/not in" condition is selected for a text field, version 5.1.14 supports searching for strings containing spaces in the filter box.

FineBI also supports searching for characters containing spaces by fuzzy search within the filter box, as shown in the following figure.

2.2.2 Set numeric field filter

Since you need to filter out the data where the product name is "1" or "contract amount" is greater than the average, you need to take the "or" relationship for both conditions.

Click "Add condition (or) > Please select field" and select the "contract amount" value field, as shown below.

Set the contract amount greater than the average value condition, then the system will automatically calculate the average value of this contract amount, and then filter out the contract amount greater than this average value according to the set condition, as follows.

The optional conditions for the numeric field filter are"between, is not between, equal to, not equal to, greater than, less than, greater than equal to, less than equal to, largest N, smallest N, empty, is not empty".

Note: where greater than, less than, greater than equal to, less than equal to can choose fixed value or average value when filtering, fixed value is the manually entered value, while the average value is the system automatically calculates the average of the value field and filter according to the conditions.

2.2.3 Set time field filter

Since you need to filter out all the data that meet the filter conditions set in 2.2.1 or 2.2.2 or "contract signing time" relative to "four years before the current time", you need to continue to take the "or" relationship.

Click "Add condition (or) > Please select field" and select the "Contract signing time" time field, as shown in the following figure.

If you need to dynamically filter all the data of "contract signing time" relative to "four years before current time", you can add a filter condition that "contract signing time" belongs to the year start relative to current time four years ago to the year end relative to four years before current time, as shown in the following figure.

The optional conditions for the time field filter are "in、not in、some date before、some date later、equal to、not equal to、date the earliest N、date the latest N、is empty、is not empty".

Note: Date and time options include fixed time and dynamic time. Fixed time is a fixed year/month/day option, and dynamic time can be set as a condition relative to the current time, which will change dynamically according to the current time when previewing data.

2.3 Add Formula Filter

Add Formula supports formula filtering for currently added table fields, embedded with common calculation symbols (addition, subtraction, multiplication, division, brackets) and functions, and the system will automatically detect whether the formula is legal when you enter it.

Add the condition of contract unit price, filter out the data whose "contract unit price" is greater than one million, set the condition as "contract amount / purchase quantity > 1000000", click "OK", as shown in the following figure.

Note: The fields in the formula need to be selected by clicking on the field box and are not valid for manual input.

2.4 Effect view

At this point, filter out the data that meet "any one of the three conditions added" and that meet "contract unit price greater than 1000000", as shown below.

Click "Save" to use the data, as shown below.

3. Filter data volume display

3.1 The number of calculation displayed

The self-service dataset filter editing interface displays only the first 5000 rows of the calculated results, as shown in the following figure.

3.2 The number of actual calculation

3.2.1 Enter filter items manually

When using "Data Processing User" to create self-service dataset in data package, since the default data is calculated using the "first 20,000 rows" and cannot be switched, sometimes the fields selected for filtering may not display all filter items due to the actual number of calculations, at this time you can add and select filter items by "entering filter items manually".

For example, use the "FRDemo_PersonnelAlteration", select all fields, add filter settings, add the condition to select the "Staff name" field, set the condition to "in", manually enter the field "Xiao Hong" and click "add", as shown in the following figure.

Note: Data analysis users who are set to use the first 5,000 rows of data calculation can also use this scheme.

Then add the filter item to the filter criteria successfully and click "OK", as shown in the following figure.

3.2.2 Calculation result appears to be empty after filtering

When using the first 5000 rows of data or the first 20,000 rows of data for the calculation, the result of the filtering may appear empty, as shown in Section 3.2.1, for example.

However, this situation does not affect the calculation processing of the data and the display of the dashboard of the self-service dataset. In the preview screen of the self-service dataset data, the first 5000 rows of all data calculation results can still be displayed, and the number of rows displayed can be modified manually.