Table filtering

1.1 Version

1.2 Application scenarios

The users need to filter the data when making the table, then the functions introduced in this chapter can be used.

1.3 Function introduction

There are three filtering methods when making table components after aggregation:

• Analyze filter settings for the area field.

• Detail filtering.

• Result filter.

The scope of use of filtering methods is shown in the following table:

For the detailed introduction of the difference between the above three different filtering functions, please refer to: Visual Filter Summary.

The analysis of area field filtering is shown in the following table:

The filtering of analysis area fields is introduced in two parts, namely "dimension field" and "Indicator field". The example in this chapter uses the grouping table made by the Demo data "FRDemo_ORDERS". The filter setting of the analysis area has two entries: "Dimension Indicator Bar" and "Header Filter", as shown in the following figure:

Note: When the table header is filtered, it is subject to the "quantity access limit" (default value 1000000). If the amount of data is too large, an error may be reported if the limit is exceeded. It is recommended to use the self-service data set to filter first.

It can also be modified in "System Management> General> BI Parameters". The parameter affects the memory, you need to be careful when modifying it, and adjust it according to your own memory size. For details, you can refer to: The current table data exceeds the maximum number of rows.

2.1 Dimension filtering

Move the mouse to the dimension field to be filtered, and select "Drop Down>Filter", as shown in the figure below:

The system automatically pops up the filter setting box, and the filter condition of the analysis area dimension field supports "Add Condition" and "Add Formula". As shown below:

2.1.1 Add condition

1) Select the field. Supports selection of the current dimension and Indicator fields that have been dragged into the analysis area, as shown in the following figure:

2) Select filter condition.

When the "In/not in" condition is selected in the text field, the 5.1.14 version supports searching for strings containing spaces in the filter box, as shown in the following figure:

You can use fuzzy search to search for characters containing Spaces in the filter box, as shown in the following figure:

Note: The top N with the largest ASCII code in the text field: sort the text in the field according to the ASCII code, and take the top N; the top N with the largest ASCII code in the date field: sort the date by morning and evening, and take the latest N Dates.

When the grouping type is year, year, quarter, year, month, year, week, year, month, and day, the time field supports the filter condition to be set to in/not in the "filter component value", and the text field to support the filter condition to be set to in/not in the text filter component ,As shown below:

Note: When the text list filter component selects a custom value list, if the drop-down value is not set, it corresponds to the input box, and the blank area prompts: please enter the specific filter value.

As shown below:

2.1.2 Add formula

Click "Add formula>Please edit formula", and the formula setting box will pop up. As shown below:

1) For formula support functions, please refer to functions.

2) The supported operators include addition, subtraction, multiplication, division, and parentheses, as shown in the figure above.

3) Only the numeric fields that have been dragged into the analysis area can be used as the conditions of the filter formula.

2.2 Indicator filtering

Touch the indicator field in the analysis area with the mouse, click drop-down> filter, as shown in the figure below:

The system automatically pops up the filter setting box, and the analysis area indicator column field only supports adding conditions, as shown below:

2.2.1 Add conditions

The setting method of adding conditions for indicator fields is the same as that for dimension fields. For details, please refer to section 2.1.1 of this chapter.

2.3 The relationship between multiple filter conditions

2.3.1 Add multiple conditions

If you need to add the next condition, click "Add Condition", as shown in the figure below:

Click the button directly, and the condition relationship added by default is the value in parentheses after "Add Condition". Pull down to switch the relationship of adding conditions, as shown in the following figure:

When adding multiple filter conditions, whether it is a condition or a formula, the relationship between multiple filters includes two types: and and or.

2.3.2 Complex use of and or relations

Sometimes users need to perform complex nesting of conditions or formulas. For example, to achieve the following situations, as shown in the figure below:

2) Click "drop-down>and condition" to add condition, as shown in the figure below:

3) Click Please select the field to add filter conditions, as shown in the figure below:

4) Click the contract amount and select the drop-down> or condition to add condition, as shown in the figure below:

5) Supplement the last filter condition, the final effect is as shown in the figure below:

When making components, users do not need to use some of the data in the original data, and can use detail filtering to filter out data that does not need to participate in the calculation, and only use the required data to make dashboards. The table component has two detail filtering entries: summary table entry and schedule entry. The detail filtering is shown in the following table:

Note: It is not possible to filter the calculated indicators in detail, please refer to section 3.3.2 in this chapter for details.

3.1 Detail filtering entry

The entry of the summary table is different from that of the schedule.

3.1.1 Summarty table entry

The summary table can only perform detailed filtering on Indicator fields, and select "drop-down> detail filtering" on the Indicator field, as shown in the figure below:

3.1.2 Schedule entry

The schedule can filter the dimension fields and Indicator fields in detail. You can select "drop-down> detail filtering" in the field to be analyzed on the left, or select "drop-down> Detail Filtering" in the analysis area, as shown in the following figure:

3.2 Add filter conditions

After entering from the entries, the system pops up the detailed filter setting interface, you can choose to add conditions or add formulas, as shown in the following figure:

3.2.1 Add condition

1) Select the field. You can select all dimensions and indicator fields of all areas to be analyzed, as shown in the following figure:

2) Select filter conditions

The text field can choose to in, not in, contain, not contain, is empty, is not empty, begin with, end with, not begin with, not end with, as shown in the following figure:

When the 5.1.14 version text field selects the "in/not in", it supports searching for strings containing spaces in the filter box, and supports searching for characters containing spaces through fuzzy search in the filter box. The effect is the same as the field to be analyzed in section 2.1 filter.

The condition of the numeric field can be between, not between, equal to, not equal to, greater than, less than, greater than, equal to, less than or equal to, empty, not empty, as shown in the following figure:

3.2.2 Add formula

Add formula supports all fields in the area to be analyzed as formula conditions, as shown in the figure below:

For formula support functions, please refer to Functions. The supported operators include addition, subtraction, multiplication, division, and parentheses, as shown in the following figure:

3.3 Matters needing attention

3.3.1 Detail filtering display rules

1) Single Indicator detailed filtering

When there is only one indicator in the table, when the indicator is filtered in detail, the table will cancel the entire row of values that do not in the filter condition.

For example, the following table has only one Indicator field "Value 3", and detailed filtering is performed on "Value 3". The filter condition is value 3>0, then the entire row of values less than 0 in the "Value 3" field is removed, as shown in the figure below:

2) Detailed filtering of multiple indicators

When the component has multiple indicators and detailed filtering is set, because the table needs to display the entire row of data, the entire row will not be canceled, but the non-empty data that does not meet the filter conditions will be displayed as empty. The original empty data Still empty. After the data shows a null value, it does not participate in the summary calculation.

For example: the following table has two Indicator fields "Value 3" and "Number of Records", the "Value 3" is filtered in detail, the filter condition is the value 3>0, and the non-conforming value is displayed as empty, as shown in the following figure:

3.3.2 Calculation indicators cannot be filtered in detail

1) Detailed filtering of calculated indicators is not possible.

2) If the Indicator field has been filtered in detail, then when the field is used in the calculation of the Indicator, it is necessary to use the aggregate function for processing.

For example: the "Purchase Quantity" field has been filtered in detail before, and the Indicator after detailed filtering only supports aggregate functions, as shown in the following figure:

Note 1: The schedule does not support the result filter.

Note 2: Real-time data "date field" is used to filter the results. If the date format is "year", "quarter", "month", "week", "week", "day", "hour", "minute", "second" can be set to conditions as in, not in, contain, not contain, is empty, is not empty, begin with, end with, not begin with, not end with, top N, last N; conditions that can be set for other date formats Only: in, not in, is empty, is not empty, top N, last N.

The result filter is suitable for scenarios where you do not want a field to be displayed in a chart or table, but you need to set the field as a filter condition. You can drag multiple fields into the result filter to set multiple filter conditions.

For example, if you need to filter out the data that has been delivered, but you don't want to add a column of "Is it delivered" in the table, you can use the result filter.

4.1 Operation steps

1) Drag the field into the result filter. All dimensions and indicators (including calculated indicators) support the drag-in of the result filter, drag the "has been delivered", as shown in the following figure:

2) Hover the mouse over the field that has been dragged into the result filter, and select "drop-down> filter", as shown in the figure below:

3) Enter the filter setting box, you can add conditions and add formulas. For detailed settings, please refer to section 2.1 of this chapter.

Click "Add Condition" to set the filter condition for paymethod, as shown in the figure below:

In version 5.1.14, when the text field selects the "in/not in", it supports searching for strings containing spaces in the filter box, and supports searching for characters containing spaces through fuzzy search in the filter box. The effect is the same as the area to be analyzed in section 2.1 Field filtering.

4.2 Effect view

Click "OK", and the data in the table on the right is filtered to the result of paymethod. As shown below:

In order to optimize the performance of the dashboard, reduce memory pressure, and improve the user experience, the 5.1.14 version of the direct connection has made some optimizations on the calculated filter value.

1) When performing "Analysis Area Filtering" or "Header Filtering" or "Result Filter Filtering" for "Dimension Fields", the Indicator fields that have been "quickly calculated" and those that use the quick calculation cannot be displayed.

For example, this table uses fast calculation and fast calculation, as shown in the following figure:

When performing dimension field filtering on the "Platform" field, you cannot select the indicator fields that use the quick calculation and quick calculation functions in the indicator column, as shown in the following figure:

2) If a certain indicator field (including common indicators, aggregate/non-aggregated indicators, and number of records) is set to "Quick Calculation" and the indicator is filtered (non-detailed filtering), the data displayed after filtering will not be affected The total data display of other indicator fields (using quick calculation, deduplication count, etc.) under the indicator column is as shown in the figure below:

At the same time, if you set filter conditions for indicators that use the quick calculation, it will not affect the indicator data that uses the "quick calculation" in the column.