Concepts → Incorta View Formula Column
About an Incorta View formula column
For an Incorta View, you can create an expression for a formula column using the Formula Builder. A formula expression has certain capabilities and limitations in an Incorta View.
Using the Analyzer, you can create an Incorta View formula column, in the Analyzer, by dragging and dropping Add Formula from the Data panel into the desired Insight panel tray. This will add the new formula column to the Incorta View. From here, you can edit the formula expression in the Formula Builder.
Properties of a formula column in an Incorta View
You can specify and edit the properties of a formula column in an Incorta View using the Analyzer and can review the read-only properties in the Business Schema Designer.
For an Incorta View, there are two types of visualizations available for selection in the Insight Panel of the Analyzer: Listing Table and Aggregated Table. Available trays and corresponding properties differ for each type of visualization.
Label and Name Properties
Incorta automatically creates the value for the Name property using the value of the Label property.
Incorta Function Property: Measure and Dimension
A formula column in a Measure tray automatically functions as a measure. A formula column in a Grouping Dimension tray automatically functions as a dimension.
Incorta View editable properties
In the Analyzer, a formula column of an Incorta View is a pill in either a Grouping Dimension or Measure tray. A Listing Table does not have a Grouping Dimension tray.
Editable measure properties for a formula column in a Listing Table
These properties are only applicable to a Listing Table visualization for an Incorta View.
| Property | Control | Description | Configuration |
|---|---|---|---|
| Label | text box | Enter a user-friendly name in the pill. | utf-8 md valid characters, including emoji 😀 To edit the Label property, you must double-click the pill itself, and enter a label value. |
| Formula | text box | Shows an empty or a valid formula expression. | Select to open the Formula Builder. Use a casting or conversion function to explicitly set the data type. |
| Base Field | drag and drop target box | Shows a base field for the query plan. | From the Data panel, select a column to drag & drop to the target box. The selected column represents the join path for the formula expression query by identifying a child or parent key column. |
Measure properties for a formula column in an Aggregated Table
These properties are only applicable to an Aggregated Table visualization for an Incorta View.
| Property | Control | Description | Configuration |
|---|---|---|---|
| Label | text box | Enter a user-friendly name in the pill | utf-8 md valid characters, including emoji 😀 To edit the Label property, you must double-click the pill itself, and enter a label value. |
| Formula | text box | Shows an empty or a valid formula expression. | Select to open the Formula Builder. Use a casting or conversion function to explicitly set the data type. |
| Aggregation | drop down list | Specify an aggregation type, formula, or none. | |
| Filter | drag and drop target box | Specify a measure filter | In the Filter panel, configure the filter properties. |
| Base Field | drag and drop target box | Shows a base field for the query plan | From the Data panel, select a column to drag & drop to the target box. The selected column represents the join path for the formula expression query by identifying a child or parent key column. |
Grouping dimension properties for a formula column in an Aggregated Table
These properties are only applicable to an Aggregated Table visualization for an Incorta View.
| Property | Control | Description | Configuration |
|---|---|---|---|
| Label | text box | Enter a user-friendly name in the pill. | utf-8 md valid characters, including emoji 😀 To edit the Label property, you must double-click the pill itself, and enter a label value. |
| Formula | text box | Shows an empty or a valid formula expression. | Select to open the Formula Builder. Use a casting or conversion function to explicitly set the data type. |
| Show Empty Groups | toggle | Enable to show empty group values from the parent table. | Enable or Disable; default is disable. |
A formula column that functions as a dimension in an Incorta View will not appear in the list of runtime dashboard filters.
You can review the read-only properties of a formula column in an Incorta View in the Business Schema Designer.
Here are the properties of a formula column in an Incorta View:
| Property | Control | Description | Configuration |
|---|---|---|---|
| Name | read only | The Label property will determine the Name property. | utf-8 md valid characters; excluding spaces Incorta replaces spaces with underscore characters. |
| Label | read only | Editable in the Analyzer by double-clicking on the label of the formula column pill. | |
| Data Type | read only | The Incorta Data Type is determined by the formula return type. | |
| Function | read only | Incorta determines the Function of a formula column based on how it is used in the Analyzer. A formula column in a Measure tray automatically functions as a measure. A formula column in a Grouping Dimension tray automatically functions as a dimension. | Dimension or Measure |
Incorta View formula column expressions
A formula expression returns a scalar value or array of values. These values are of a specific type. A formula expression can consist of:
- referenced columns
- built-in functions
- variable
Referenceable columns in an Incorta View formula column expression
A referenced column has a fully qualified name. Here are the possible types of referenceable columns in an Incorta View formula expression:
| Reference Source | Reference Type | Referenceable | Note |
|---|---|---|---|
| Physical_Schema.Table.Column | Data-backed | Yes | |
| Physical_Schema.Table.Formula_Column | Data-backed formula | Yes | |
| Physical_Schema.Incorta_Analyzer_Table.Column | Data-backed | Yes | |
| Physical_Schema.Incorta_Analyzer_Table.Formula_Column | Data-backed formula | Yes | |
| Physical_Schema.Incorta_SQL_Table.Column | Data-backed | Yes | |
| Business_Schema.View.Column | Data-backed | Yes | |
| Business_Schema.View.Formula_Column | Runtime formula | Yes | |
| Business_Schema.Incorta_View.Column | Data-backed | No | Incorta View can not depend on another Incorta View column |
| Business_Schema.Incorta_View.Formula_Column | Runtime formula | No | Incorta View can not depend on another Incorta View column |
Data-backed references data that persists to Shared Storage.
Supported Built-In Functions
A formula column in an Incorta View can use the following built-in functions in a formula expression:
| Function Type | Description | Example |
|---|---|---|
| Aggregation | Functions that accumulate data to a summary value | average, count, distinct, max, median, min, or sum |
| Boolean | Functions that evaluate to True or False | and, between, contains, endsWith, in, inList, isNan, isNull, like, not, or, startsWith |
| Conditional Statement | Statements that return a value based on the evaluation of a parameter | case, decode, and if |
| Conversion | Functions that convert the Incorta Data Type of a parameter. In addition, functions that manipulate Timestamp and Date values for different return values. | day, double, monthName, parseDouble, string, toChar, and year |
| Filter | Functions that specify a subset of data items | firstVersion or lastVersion |
| Miscellaneous | Functions with assorted uses | descendantOf, lookup, rowNumber, and schemaRefreshTime |
| Arithmetic | Functions that take one or more values and return a single value | abs, ceil, exp, floor, max, min, mod, rnd, round, sqrt, and trunc |
| Date | Functions that manipulate Timestamp and Date values | addDays, date, month, removeTime, weekday, and year |
| String | Functions that return string values or return values based on string parameters | bin, concat, find, ifNull, repeat, rTrim, and upper |
| Analytic | Functions that compute values over a group of rows and returns a single result for each row | denseRank, index, or rank |
Variable references in formula expressions
In the Formula Builder, in the Functions and Variables panel, certain variables are referenceable in the formula expression. Some types of variables all users have access to and other kinds of variables are subject to Discretionary Access Control (DAC).
Here are the various categories of variables:
- System Variables: Predefined variables within Incorta that return a value as a defined type.
- External Session Variables: Variables that query an external data source when invoked. Values returned as string.
- Internal Session Variables: Variables that query data within a schema. Values returned as string.
- Global Variables: A static variable available to all tenant users that return a value as a defined type.
Formula expressions in an Incorta View can reference the following variables:
| Variable Type | Referenceable | Note |
|---|---|---|
| System Variable | Yes | |
| External Session Variable | Yes | |
| Internal Session Variable | Yes | |
| Global Variable | No | Not supported in 4.9 or earlier releases |
Additional Considerations
There are additional considerations for an Incorta View formula column in:
- the Analyzer
- an Insight
Incorta View formula column in the Analyzer
When using the Analyzer, you can select and inspect the columns of an Incorta View in the Data panel. For a given column, select the Information icon in the right gutter. The Column Details dialog shows the:
- Column Name
- Source Column Name/Formula*
- Data Type
- Function
- Description*
- Sample Data*
The Column Details dialog dialog does not show the Source Column Name/Formula or Description. Sample Data show a row-level calculation and not aggregated calculations.
Incorta View formula column in an Insight
An Incorta View formula column in an Insight will enforce a defined aggregation in the formula expression. This means that a user cannot override the aggregation for the Incorta View formula column in the Analyzer.
You can use an Incorta View formula column as an applicable filter for an insight, including Individual, Distinct, and Aggregated filters.