on 03-08-202203:07 PM - edited on 03-15-202201:54 PM by KailaT
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.
What you should know before reading this article
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.
Business Schema Formula Columns
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. These limitations 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.
Here are some use cases for moving formulas into a Business Schema:
Reuse a specific formula across multiple Insights
Need to change the data type. 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
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 Schema formula column cannot be used in another Schema formula column but can be used in a Business Schema formula column.