|
1. Overview Calculated columns allow the creation of new data columns using data columns that already exist in the dataset. That is, based on an existing column, a new column (or field) is created whose values or members are determined by the controlled calculation. In vividime, new calculated columns can be created in both datasets and reports. Calculated columns created in datasets are stored in datasets, and calculated columns created in reports are stored in reports. New calculated columns can be used to bind data for charts, text, parameters, filters and other components. ➢Description: The raw data remain unchanged. 2. Why use calculations Calculated fields are required for many reasons. ➢Example: •Splitting the data •Converts the data type of a field, such as converting a string to a date. •syndicated data •Screening results •Calculate the percentage of Common scenarios are as follows: 2.1 Data columns required for analysis are missing from the data set ➢Example: If there are [Sales] and [Profit] fields in the dataset and you want to calculate [Cost], you can create a [Cost] field using a formula similar to the following. 2.2 Want to Convert Data in a Component ➢Example: To calculate the percentage of sales in different markets, you can use the dynamic calculation as shown below: 2.3 Want to Categorize Data Quickly ➢Example: If you want to quickly color the profit data in a visualization item as Profit Achieved and Profit Not Achieved. You can create calculated fields using a calculation similar to the one below and then add them to the Color on the Marker Groups card. IF SUM(col["profit"]) > 50000 THEN "reached" ELSE "not reached" END 3. Creating, using and editing calculated columns After determining the type of calculation you want to use, it's time to create the calculation columns. 3.1 New calculated columns on the dataset 3.1.1 Creation It is possible to create calculated columns based on one of the existing columns in both the metadata and data details pages. •Create calculated columns in the metadata page. •Create calculated columns in the Data Details page. Select New Calculated Column to bring up the Calculated Columns dialog box. 3.1.2 Editing On the Metadata and Data Details page, calculated columns created on a dataset can be edited by clicking the More icon and selecting " Edit Calculated Columns". 3.1.3 Use Save the dataset, create reports based on this dataset, add data components such as charts, etc., i.e. you can drag and drop the calculated columns created on the dataset into the component. ➢Attention: Calculated columns created on the dataset cannot be re-edited on the report side, only on the dataset. 3.2 New calculated columns in reports 3.2.1 Creation In the Binding area, select the bound dataset, click the More icon, and select New Calculated Column. The Calculation Columns dialog box will pop up. You can see that the list of functions includes basic calculations and multi-granularity expression calculations. 3.2.2 Editing Calculated columns created on a report can be edited by clicking on the More icon and selecting " Edit Calculated Columns". 3.2.3 Use Drag the created calculated columns directly into the component. Different components in the same report that use this dataset will also be able to see the calculated columns created on the report side of this dataset. 3.3 Calculations in Component Dynamic Calculations 3.3.1 Quick Calculation Columns On the Component Binding page, selecting the drop-down menu button for the Summary column and selecting the predefined Dynamic Calculator allows you to use the predefined dynamic calculations, and the component data is recalculated based on the selected calculations. For more instructions on dynamic calculations, please refer to Data Processing and Analysis -> Dynamic Calculations. 3.3.2 Adding customized dynamic calculation columns On the Summary column, click Add Dynamic Calculation. In the Dynamic Calculation dialog box that opens, select Customize. In the Calculation Columns dialog box that pops up, enter the calculation. You can see that the list of functions includes basic and dynamic calculations. Click OK to enter a dynamic calculation name By clicking OK, the component's bound column becomes a dynamically calculated column, and the component's data is recalculated based on the defined calculations. 3.4 Use of computed columns on filing forms In the Fill Form, if the data to be entered in a column does not need to be filled in manually, but needs to be calculated from the filled in data, you can use a calculation expression to do the calculation. Select a column on the Fill Form component and click [Settings > Calculation Expressions] in the right edit field. The Calculate Expression dialog box is shown below. You can see that the list of functions contains basic calculations. The data columns will only list the columns bound to the component, i.e. calculations can only be performed based on the data populated in the data columns of the component. For example, if you have a database table with fields Sales, Profit, and Cost, and you want to enter Sales, Profit, and Cost into this database table. Create a SQL dataset based on this table, bind sales, profit, cost to the reporting component, the cost column can be calculated from "Sales - Profit" and does not need to be filled manually. For more instructions on how to fill out the form, please see: Data Filling. 3.5 Other notes Before 8.8 and after 8.8, the product supports different calculated column functions and processing logic. Starting from 8.8, vividime has made enhancements and optimizations to the calculation column functions. The main optimization points are: •Portal Optimization. When creating a new calculated column in a report, there used to be two entry points: "New Expression" and "New Aggregate Expression". In version 8.8 and later, there is one entry point: New Calculated Column. •Remove the data type selection box. Previously, you needed to select the type of data returned by the calculation column. In version 8.8 and later, it is no longer necessary to select the data type, and the product automatically determines the type of data returned. •Change the computation engine. Previously, it was divided into SQL expressions and non-SQL expressions (using JavaScript syntax). In 8.8 and later versions, it is divided into basic functions, SQL statements and Mongo pipeline statements. The basic function is the function supported by vividime product, which adopts the syntax specified by the product, and if it is directly connected to the database, it can be converted to SQL syntax and pushed to the database for execution.SQL statement should be written according to the SQL syntax, and the product doesn't do the conversion, and directly combines the written content into SQL statement and pushes it to the database for execution.Mongo pipeline statement is for the Mongo dataset, and it supports writing the syntax supported by the Mongo pipeline statement. Mongo pipeline statement is for Mongo dataset, support to write according to Mongo pipeline statement syntax, the product does not do the conversion, and will directly combine the written content into the pipeline statement and push it to the database for execution. •Performance Optimization. Previously, non-SQL expressions were processed using the JavaScript engine, and the computed columns were all computed in memory. 8.8 and later releases have also pushed down the database processing for the base functions for better performance. V10.2 and later: Calculation type support JS statement for compatibility with older JS syntax, configure support.calc.type=js if needed.
| 
