1. Home
  2. Cloud
  3. Guides
  4. Tours
  5. Concepts
    1. Aggregate filter
    2. Alias
    3. Apache Avro™
    4. Apache Kafka™
    5. Applied filter
    6. Base table
    7. Bookmark
    8. Business schema
    9. Business schema view
    10. Business schema view column
    11. Business schema view formula column
    12. Column
    13. Connector
    14. Dashboard filter
    15. Dashboard folder
    16. Dashboard runtime filter
    17. Data agent
    18. Data alert
    19. Data destination
    20. Date system variable
    21. Distinct filter
    22. Dynamic Field
    23. Expression
    24. External data source
    25. External session variable
    26. Filter expression
    27. Filter expression session variable
    28. Filter option
    29. Formula column
    30. Formula expression
    31. Full load
    32. Global variable
    33. Incorta Analyzer Table
    34. Incorta Analyzer Table column
    35. Incorta Analyzer Table formula column
    36. Incorta Analyzer View
    37. Incorta Analyzer View formula column
    38. Incorta SQL Table
    39. Incorta SQL View
    40. Individual filter
    41. Insight filter
    42. Internal query expression
    43. Internal session variable
    44. Join
    45. Join condition
    46. Join condition column
    47. Join filter
    48. Join filter column
    49. Level based measure
    50. Load filter
    51. Local data file
    52. Local data folder
    53. Materialized View
    54. Materialized View Column
    55. Materialized View Formula Column
    56. Measure filter
    57. Miscellaneous system variable
    58. Multi-Source table
    59. Physical schema
    60. Physical schema table
    61. Physical schema table column
    62. Physical schema table formula column
    63. Physical schema table runtime security filter
    64. Presentation variable
    65. Prompt
    66. Query system variable
    67. Runtime business view
    68. Runtime business view formula column
    69. Runtime security filter
    70. Session variable
    71. Shared Storage
    72. Upgrade Readiness
    73. Variable
    74. Visualization
  6. Tools
  7. References
  8. Integrations
  9. Data applications
  10. Releases

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.

Column data lineage

Starting with 2023.7.0, you can view the data lineage of columns and formula columns in runtime business views in the Data Lineage Viewer v2 that displays a diagram with the entities referenced in the column (upstream lineage) and entities where the column is referenced (downstream lineage).

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.
Typeread onlyThe Incorta Data Type is determined by the formula return type.
Descriptiontext boxEnter a description for the formula column
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
Note

Starting with the 2024.1.0 release, you can use the integrated AI tools to generate and suggest descriptions and labels for selected columns in a runtime business view. For details, see AI-supported features for business schemas.

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
Physical_Schema.Table.ColumnData-backedYes
Physical_Schema.Table.Formula_ColumnData-backed formulaYes
Physical_Schema.Incorta_Analyzer_Table.ColumnData-backedYes
Physical_Schema.Incorta_Analyzer_Table.Formula_ColumnData-backed formulaYes
Physical_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 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.