In the previous article, we explained the data editing function of the BI tool and its specific operation process (the process content is very detailed, have you read it?). In this article, we will continue to explain the third feature inventory of BI tools: super functional capabilities, without further ado, let's get started!
Previous articleGuiding the way》BI tool function inventory 2: data editing.
The articles and pictures are all built by Finebi, a star product of Finesoft!
Pay attention to Finesoft and continue to explain data analysis methods and enterprise digital transformation tools and solutions to you
1.What is Functional Capability?
What we usually call functional ability usually refers to the ability to understand and use functions in mathematics or computer science. Whereas, in data analysis, functional capability refers to the ability to use various functions to process and analyze data. This includes some common functions, such as mean, median, standard deviation, etc., as well as some domain-specific functions, such as the IRR function in finance or the t-test function in statistics.
Having good functional abilities allows data analysts to do their jobs more efficiently and be able to analyze and interpret data more accurately. In addition, proficiency in various functions can also help data analysts uncover trends and patterns hidden in the data, which can lead to better decision-making.
2.What are the function syntax?
Field syntax. You can insert fields into calculations. The syntax of a function will often indicate where a field should be inserted in a calculation. For example: sum agg(array).
For example, if you want to calculate the average contract unit price, the calculation will use the Contract Amount and Purchase Quantity fields in the data source, enter the formula: sum agg (contract amount) sum agg (purchase quantity), and click to select the required field in the field selection area on the left, as shown in the following figure
Operator syntax
To create a calculation, you need to understand the operators supported by FineBI. Operators are shown in black in Finebi calculations.
Text expression syntax
This section describes the correct syntax for using text expressions in BI tool calculations.
Text expressions represent constant values as-is. When using functions, sometimes you need to use text expressions to represent numbers, strings, dates, and so on. Text expressions are displayed in black and gray in BI tool calculations.
1.Regular functions
A regular function is a set of functions used in BI tools to perform a variety of common operations and calculations. These functions cover aspects such as logic, mathematics, trigonometric functions, dates, and text processing. Here are some examples of common general functions:
Logical function: if: Returns different values based on the condition.
and: Checks whether multiple conditions are valid at the same time.
or: Checks whether one of the multiple conditions is true.
not: Negation of a given condition.
Mathematics and Trigonometry:
min: Returns the smallest value in a set of numbers.
max: Returns the maximum value in a set of numbers.
sum: Calculates the sum of a set of numbers.
*g: Calculate the average of a set of numbers.
sin: Calculates the sine value for a given angle.
cos: Calculates the cosine value for a given angle.
tan: Calculates the tangent of a given angle.
Date function: month: Returns the month of the given day.
year: Returns the year for a given date.
day: Returns the day for a given date.
today: returns the current date.
Text function: substitute: Replace part of the text.
concatenate: Merges multiple text strings into one.
left: Captures the character of the specified length to the left of the text.
right: Captures the character of the specified length to the right of the text.
len: calculates the length of the text.
2.Aggregate functions
Different aggregate functions can summarize the data as needed, resulting in different descriptions and statistical results of the data set.
Here are some common aggregate functions and what they do:
Sum: Sums all the values of the specified field and returns the sum.
Average (**g): Returns the average value by adding all the values of the specified field and dividing by the number of values.
Median: Sorts all values of a specified field by size, and then returns the median value. If the number of values is even, the average of the middle two numbers is returned.
Max: Returns the maximum value of the specified field.
Min (min): Returns the minimum value of the specified field.
Standard Deviation (STDEV): A measure of how discrete the dataset is, i.e., how much the mean of the data differs from each data point.
Variance (VAR): A measure of how discrete a data set is, i.e., the square of how much the mean of the data is different from each data point.
Count distinct: Returns the number of deduplicated values for the specified field.
Count: Returns the number of values for the specified field.
3.def function
The def function can be used in combination with other basic functions to output computing metrics of any level and complexity on limited data.
The main function of the def function is to define user-defined calculation logic or expressions. By writing custom solutions, you can implement more flexible and complex computing requirements in BI tools to meet the requirements of different business scenarios.
In BI tools, in addition to def functions, there are other types of analysis functions, such as def-add, def-sub, and earlier functions, which provide more computing power, can solve some implementation problems, and make user solutions more flexible and powerful.
Overall, the DEF function and other analysis functions provide BI users with more advanced and flexible computing functions, so that they can better meet user needs in data analysis and business scenarios.
Continuing from the previous article, when doing data editing, it is inevitable that you will encounter some problems, which cannot be completely solved by the data editing function of BI tools, so at this time, function computing comes in handy.
1.Determine the type of function calculation
Self-service dataset calculation: The data source detail level (row detail level calculation) is performed in the new column of the self-service dataset, which can be used to create visualization components in the dashboard, and the results of the dataset calculation can also be used by other users. Note: Aggregate calculations are not supported.
Dashboard calculations: When you make a dashboard component, you can add calculation fields to aggregate calculations or calculations in other self-service datasets, and the calculation results of the component will not be saved in the dataset.
Create fields using self-service datasets
In FineBI, go to the analysis topic editing interface under My Analysis, select the data, and click Edit, as shown in the following figure:
Add the new columns as shown in the following figure:
Select the Formula function and enter a name for the calculated field of the new column. In this example, the field is called test, enter the formula, and this example uses the following formula: if(find("on", store name), 1,0), if the field contains on, it returns a 1, otherwise it is displayed as 0, and when it is done, click OK, as shown in the following figure:
Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually. Saved self-service datasets are available to others and can be used when creating components for dashboards.
The dashboard creates calculated fields
Dashboards support aggregate functions more than self-service datasets.
Add a component and select the store sales statistics table, as shown in the following figure
Create a new field Cumulative Payment Amount, and enter the formula: acc sum(sum agg(sales),1), which means that the sales amount is summed up according to the dimension, as shown in the following figure
Once completed, it can be used in the component.
Complete BI teaching
In summary, the super-functional capabilities of BI tools play a crucial role in today's information age. Through the rational use of various statistical and mathematical functions, we are able to extract valuable insights and trends from large amounts of data to provide a scientific basis for decision-makers. Not only that, but the function capability can also help us build an efficient data processing process to improve work efficiency and accuracy.
Thank you for reading and supporting, if you still have any questions about the super function capabilities of BI tools, please feel free to leave us a message in the comment area or background private message, and we will get in touch with you in time!
Big data visual analysis tools