Concepts → Incorta Analyzer View Formula Column

About an Incorta Analyzer View formula column

For an Incorta Analyzer 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 Analyzer View.

Using the Analyzer, you can create an Incorta Analyzer 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 Analyzer View. From here, you can edit the formula expression in the Formula Builder.

Properties of a formula column in an Incorta Analyzer View

You can specify and edit the properties of a formula column in an Incorta Analyzer View using the Analyzer and can review the read-only properties in the Business Schema Designer.

For an Incorta Analyzer 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 Analyzer View editable properties

In the Analyzer, a formula column of an Incorta Analyzer 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 Analyzer View.

PropertyControlDescriptionConfiguration
Labeltext boxEnter 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.
Formulatext boxShows 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 Fielddrag and drop target boxShows 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 Analyzer View.

PropertyControlDescriptionConfiguration
Labeltext boxEnter a user-friendly name in the pillutf-8 md valid characters, including emoji 😀
To edit the Label property, you must double-click the pill itself, and enter a label value.
Formulatext boxShows 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.
Aggregationdrop down listSpecify an aggregation type, formula, or none.
Filterdrag and drop target boxSpecify a measure filterIn the Filter panel, configure the filter properties.
Base Fielddrag and drop target boxShows a base field for the query planFrom 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 Analyzer View.

PropertyControlDescriptionConfiguration
Labeltext boxEnter 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.
Formulatext boxShows 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 GroupstoggleEnable 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 Analyzer 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 Analyzer View in the Business Schema Designer.

Here are the properties of a formula column in an Incorta Analyzer View:


PropertyControlDescriptionConfiguration
Nameread onlyThe Label property will determine the Name property.utf-8 md valid characters; excluding spaces
Incorta replaces spaces with underscore characters.
Labelread onlyEditable in the Analyzer by double-clicking on the label of the formula column pill.
Data Typeread onlyThe Incorta Data Type is determined by the formula return type.
Functionread onlyIncorta 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 Analyzer 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 Analyzer View formula column expression

A referenced column has a fully qualified name. Here are the possible types of referenceable columns in an Incorta Analyzer View formula expression:

Reference SourceReference TypeReferenceableNote
Physcial_Schema.Table.ColumnData-backedYes
Physcial_Schema.Table.Formula_ColumnData-backed formulaYes
Physcial_Schema.Incorta_Analyzer_Table.ColumnData-backedYes
Physcial_Schema.Incorta_Analyzer_Table.Formula_ColumnData-backed formulaYes
Physcial_Schema.Incorta_SQL_Table.ColumnData-backedYes
Business_Schema.View.ColumnData-backedYes
Business_Schema.View.Formula_ColumnRuntime formulaYes
Business_Schema.Incorta_View.ColumnData-backedNoIncorta Analyzer View can not depend on another
Incorta Analyzer View column
Business_Schema.Incorta_View.Formula_ColumnRuntime formulaNoIncorta Analyzer View can not depend on another
Incorta Analyzer View column
Note

Data-backed references data that persists to Shared Storage.

Supported Built-In Functions

A formula column in an Incorta Analyzer View can use the following built-in functions in a formula expression:

Function TypeDescriptionExample
AggregationFunctions that accumulate data to a summary valueaverage, count, distinct, max, median, min, or sum
BooleanFunctions that evaluate to True or Falseand, between, contains, endsWith, in, inList, isNan, isNull, like, not, or, startsWith
Conditional StatementStatements that return a value based on the evaluation of a parametercase, decode, and if
ConversionFunctions 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
FilterFunctions that specify a subset of data itemsfirstVersion or lastVersion
MiscellaneousFunctions with assorted usesdescendantOf, lookup, rowNumber, and schemaRefreshTime
ArithmeticFunctions that take one or more values and return a single valueabs, ceil, exp, floor, max, min, mod, rnd, round, sqrt, and trunc
DateFunctions that manipulate Timestamp and Date valuesaddDays, date, month, removeTime, weekday, and year
StringFunctions that return string values or return values based on string parametersbin, concat, find, ifNull, repeat, rTrim, and upper
AnalyticFunctions that compute values over a group of rows and returns a single result for each rowdenseRank, index, rank, toDate, or toDate with ago

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: Variables available to all tenant users that return a scalar value or an array of scalar values as a type defined per the query syntax.

Formula expressions in an Incorta Analyzer View can reference the following variables:

Variable TypeReferenceableNote
System VariableYes
External Session VariableYes
Internal Session VariableYes
Global VariableYesSupported starting the 2022.4.0 release

Additional Considerations

There are additional considerations for an Incorta Analyzer View formula column in:

  • the Analyzer
  • an Insight

Incorta Analyzer View formula column in the Analyzer

When using the Analyzer, you can select and inspect the columns of an Incorta Analyzer 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 Analyzer View formula column in an Insight

An Incorta Analyzer 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 Analyzer View formula column in the Analyzer.

You can use an Incorta Analyzer View formula column as an applicable filter for an insight, including Individual, Distinct, and Aggregated filters.