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 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 readonly 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.  utf8 md valid characters, including emoji 😀 To edit the Label property, you must doubleclick 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  utf8 md valid characters, including emoji 😀 To edit the Label property, you must doubleclick 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.  utf8 md valid characters, including emoji 😀 To edit the Label property, you must doubleclick 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 readonly 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.  utf8 md valid characters; excluding spaces Incorta replaces spaces with underscore characters. 

Label  read only  Editable in the Analyzer by doubleclicking 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
 builtin 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 

Physcial_Schema.Table.Column  Databacked  Yes  
Physcial_Schema.Table.Formula_Column  Databacked formula  Yes  
Physcial_Schema.Incorta_Table.Column  Databacked  Yes  
Physcial_Schema.Incorta_Table.Formula_Column  Databacked formula  Yes  
Business_Schema.View.Column  Databacked  Yes  
Business_Schema.View.Formula_Column  Runtime formula  Yes  
Business_Schema.Incorta_View.Column  Databacked  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 
Databacked references data that persists to Shared Storage.
Supported BuiltIn Functions
A formula column in an Incorta View can use the following builtin 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 rowlevel 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 a Incorta View formula column as an applicable filter for an insight, including Individual, Distinct, and Aggregated filters.