A formula column contains an expression that, depending on the expression and the expression context, returns a scalar or an array of values of a specific data type. An expression can consist of built-in functions, built-in system variables, and other referenceable objects valid for the expression context.
We recommend that you be familiar with these concepts in Incorta Docs.
Applies to all Incorta versions however as a growing feature there are additional options as well as syntax enforcement in release 4.8 and up.
Using the Formula Builder, there are several ways that you can create a formula column:
You can add a Formula to an Incorta Insight directly however this will only be calculated at run time and will have several limitations that can be addressed by adding the formula upstream of the insights in either a Business or Physical Schema. It is best done in an insight to quickly validate a data value or for a case where the actual formula will be specific to the Insight and not be reusable across multiple Insights.
Use cases for moving formulas into a Business or Physical Schema?
- Improve Runtime performance by performing the calculation at load time rather than run time of a Dashboard for more information please refer to Performance Tuning Guide
- Reuse a specific formula across multiple Insights
- Need to change the data type to facilitate the joins. Incorta joins require the data type to match from both sides. Implicit casting works between Long and Integer only. Examples:
- String to Integer or Long
- Long or Integer to String
- Timestamp to Date
- String process, such as substring, concatenation
- Date or Timestamp to String by taking components and form a meaningful string, such as period name
- Parse a string as a date
- Change a number, such as epoch or a JDE date to a Date column
- When a formula result is used as a grouping attribute in an Insight, it is a best practice to define the formula as a formula column in the Physical Schema
- Incorta by default disables sorting based on a formula in a list report. You must define a formula column to be used in a formula as a sorting attribute.
- Resolve the dependencies between formulas. A formula column cannot be used in another formula column but can be used in a formula expression in a business view.
- When the child table has a flexible foreign key column that is overloaded with values that refer to different tables, create different formula columns with the condition logic to return only the values from a specific parent table.