You are viewing content for 5.0 | 4.9 | 4.8 | 4.7 | 4.6 | 4.5 | 4.4 | 4.3 | Previous Releases


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 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.
Note

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
Physcial_Schema.Table.Column Data-backed Yes
Physcial_Schema.Table.Formula_Column Data-backed formula Yes
Physcial_Schema.Incorta_Table.Column Data-backed Yes
Physcial_Schema.Incorta_Table.Formula_Column Data-backed formula 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
Note

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*
Note

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 a Incorta View formula column as an applicable filter for an insight, including Individual, Distinct, and Aggregated filters.


© Incorta, Inc. All Rights Reserved.