Calculated Fields
- 28 Jun 2024
- 4 Minutes to read
- Print
- PDF
Calculated Fields
- Updated on 28 Jun 2024
- 4 Minutes to read
- Print
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
In a dashboard, calculated fields are unique expressions or formulae you design to extract new metrics or insights from your current data. Based on the information in your data set, they enable you to carry out computations, frequently integrating several variables or utilizing mathematical functions. You can create additional metrics and do an in-depth analysis of your data with calculated variables, which may not be directly present in your dataset. They help develop unique key performance indicators (KPIs) or carry out more intricate analyses directly from within your dashboard.
You can add a new calculated field based on the existing data source fields.
Create a Calculated Field
The calculated fields can be created on the Data Source page and Binding panel.
To create calculated fields, perform the following steps:
- Navigate to Dashboard Menu > Data Sources. Click Add Calculated Fields.
Figure: Add calculated fields - The edit calculated fields window opens which allows you to create expressions.
Figure: Edit calculated fields - Click Save to create a new calculated field. The newly created field is displayed in the Field List. It is indicated with f icon.
Figure: Field list
Figure: Add calculated fields
Figure: Edit calculated fieldsThe options available on the Edit Calculated fields page to create an expression are explained below.
| Field | Description |
| Fields | This option contains available fields and dashboard parameters. Figure: Fields |
| Constants | This option contains Boolean variables. Figure: Constants |
| Functions | This option contains different types of functions. To know more about the functions, refer to Functions. Figure: Functions |
| Operators | This option allows you to select operators from the list. To know more about the operators, refer to Operators. Figure: Operators |
Functions
This table contains the list of fields and their descriptions in the Functions section.
| Field | Description |
| Advanced | |
| aggr() | Aggregates the underlying data at the detail level defined by a set of predefined dimensions and a specified summary function. |
| asc() | The values in the specified column are sorted in ascending order, which is the default sort order. |
| desc() | The values in the specified column are sorted in descending order. |
| orderBy() | Defines the logical order in which the window function calculation is applied to the rows within the window. |
| partitionBy() | Defines the columns used to partition the data rows, with the window function applied separately to each partition. |
| w() | Calculates aggregated values with the specified window function for the window defined by the specified partitioning and ordering. |
| filter() | Filters underlying data with specified filter criteria and aggregates filtered data using the current detail level and the summary function. |
| joinRule() | Specifies the dimension for the 'aggr' function and a column whose values are used in the join operation after aggregation is completed. |
| Aggregate | |
| Avg() | Returns the average of all the values in the expression. |
| Count() | Returns the number of values. |
| Max() | Returns the maximum value across all records. |
| Min() | Returns the minimum value across all records. |
| Sum() | Returns the sum of all values. |
| CountNotNull() | Returns the number of non-null objects in a collection. |
| CountDistinct() | Returns the number of distinct values. |
| Median() | Returns the median of the values in a collection. |
| Mode() | Returns the mode of the values. |
| StdDev() | Returns an estimate of the standard deviation of a population, where the sample is a subset of the entire population. |
| StdDevp() | Returns the standard deviation of a population, where the population is the entire data to be summarized. |
| Var() | Returns an estimate of the variance of a population, where the sample is a subset of the entire population. |
| Varp() | Returns the variance of a population, where the population is the entire data to be summarized. |
- Date-Time - This function is used to add date and time ranges such as IsNextMonth(), IsThisWeek(), IsThisMonth(), etc.
- Logical - This function is used to add logical operators such as InRange(,), IsNull(), ToBoolean(), etc.
- Math - This function is used to add mathematical operators such as Sqr(), Cos(), Sin(), Tan(), etc.
- String - This function is used to add string operators such as Trim(), Len(), etc.
- Window - This function is used to add window operators such as First(), Last(), Size(), etc.
Operators
This table contains the list of fields and their descriptions in the Operators section.
| Operator | Description |
| + | Concatenates two strings or adds the value of one numeric expression to another. |
| - | Finds the difference between two numbers. |
| * | Multiplies the value of two expressions. |
| / | Divides the first operand by the second. |
| % | Gives back the leftover (modulus) that results from dividing a given numeric expression by another. |
| () | |
| | | Executes two numeric expressions through a bitwise inclusive OR. Evaluate each operand's bit about its matching operand's bit. Anytime one of the bits is 1, the matching resultant bit is also set to 1. If not, 0 is set in the corresponding resultant bit. |
| & | The operator AND in bitwise operations. Compares every bit in the operand's first operand to the matching bit in the operand's second. The matching resultant bit is set to 1 if both bits are 1. If not, the matching resultant bit is left at 0. |
| ^ | Operates on two numeric expressions using a bitwise exclusive OR. |
| == | If the values of both operands are the same, the return value is true; if not, it is false. |
| != | If the values of the operands are different, the function returns true; if not, it returns false. |
| < | The less than operator is utilized for expression comparison. |
| <= | The less than or equal to operator is utilized to compare expressions. |
| >= | The greater than or equal to operator is used to compare expressions. |
| > | The greater than operator is used to compare expressions. |
| In | Checks if a property is present in an object. |
| Like | Checks if a similar property in an object. |
| Between | Designates a test range. If a value is smaller than or equal to the second operand and greater than or equal to the first operand, the function returns true. |
| And | Performs a logical conjunction on two Boolean expressions. |
| Or | Performs a logical disjunction on two Boolean expressions. |
| Not | Performs a logical negation on a Boolean expression. |
Edit a Calculated Field
To edit a calculated field, click the pencil icon beside the field name.
Figure: Edit calculated field
The Edit Calculated Field screen displays. You can change the calculated field's name, type, or edit the current expression.
To delete the calculated field, use the calculated field's Delete icon.
Was this article helpful?