# Formula Builder

The **Formula Builder** makes it simple to create a complex formula expression. You can create formulas with a variety of functions — from aggregations, boolean expression, conditional statements, data conversions, data manipulations, to calculations.

You can use the Formula Builder when adding or editing:

**Formula Columns**in a schema table or business schema view**Queries**in an internal session variable**Formulas**in a filter expression session variable, dashboard filter (including prompts, applied filter, or filter options) or an insight.

To open the Formula Builder:

- Select the
**Formula**link wherever found. - Drag & Drop the
**New Formula**to a tray and then select the Formula property text box in the Properties Menu.

## Formula Builder Requirements

Users must have **View** access permissions to Incorta objects including schemas, business schemas, external session variables, internal session variables, and filter expression session variables in order to use the formula builder.

**Sorting by a Formula Column** is a very expensive operation that in certain situations can cause instability such as race conditions and out of memory errors.

To enable sorting by a Formula Column, an Administrator needs to modify the `engine.properties`

file in the Analytics Service and add the following property:

`engine.allow_formula_sorting_in_flat_table = true`

## Navigate the Formula Builder

The Formula Builder anatomy consists of the following user interface components:

**Header bar**that allows you expand, collapse, or exit the Formula Builder**Data Panel**on the left, that you can resize, expand, or collapse**Functions and Variables Panel**on the right, that you can resize, expand, or collapse**Formula Editor**with gutters on the left and right-
**Footer action bar**that allows you to**Format your calculations**or programmatic expressions in the Formula Editor.**Cancel**your changes and close the Formula Builder.**Validate & Save**your changes.

### Data Panel and Manage Data Sets Panel

By default, the **Data Panel** in the Formula Builder reflects the existing selection in the Analyzer from which it was opened. In certain cases, the Data Panel will be empty because there is no pre-existing selection context, as is the case for creating, for example, a new internal session variable.

On the left side of the page is a list of columns, pre-populated according to the underlying schema definition. To add columns from additional schemas, click the + (Add icon) on the left-hand side.

#### Manage Data Sets Panel

You can use the **Manage Data Sets Panel** to add selected schemas, business schemas, tables, or views to the Data Panel.

The Manage Data Sets Panel contains the **Views** and **Tables** tabs that can be filtered using search.

#### Data Panel

To filter and find items in the **Data Panel**, enter a search in the Search text box or use the Column Type drop down menu to narrow your results. Column Types include:

- STRING
- NUMERICAL
- DATA
- TIMESTAMP
- BOOLEAN
- KEY

For a given column in the tree, select the **information icon** to view the column details and preview sample data.

### Functions and Variables Panel

To help build a calculation or programmatic expression in the Formula Editor, you can use the Functions and Variables Panel.

In the **Functions** tab, you can filter the list of functions by entering a search term or using the Function Category drop down menu.

The function categories are:

- Aggregation Functions
- Boolean Functions
- Conditional Statements
- Conversion Functions
- Filter Functions
- Miscellaneous Functions
- Arithmetic Functions
- Date Functions
- String Functions
- Query Functions (only with Internal Session Variables)

To learn more about the function categories and individual functions, visit Formula Functions.

To **view the function syntax** that includes input arguments and associated data types and the function return data type, select a function in the function list.

In most cases, there is an example of the function’s usage and a summary description of the function’s usage.

In the **Variables** tab you will find the variables list which includes:

- System Variables
- System Date Variables
- Internal Session Variables
- External Session Variables
- Presentation Variables (only when the context is a dashboard with existing presentation variables)

To **view the Variable syntax** select the Variable in the variable list which will include the return type and descriptive summary.

### Formula Editor

The **Formula Editor** supports both code and dot completion.

For example, with the bz*sch*AdvWork_Sales business schema in the Data Panel, typing the letter “s” in the editor shows all objects with the “s” in the name:

- Built-in functions
- Business schema
- System data variables

After selecting the function, start typing the fully qualified column name in a business schema.

A fully qualified column name consists of three parts: business_schema.view.column.

Entering a period (.) triggers dot completion in the editor. To select the view, use the tab keystroke.

You can continue typing, use the tab keystroke to complete a selection, or simply select a column in the list.

#### Color Coding

The Formula Editor uses color coding to signify textual meaning as follows:

**Orange**for built-in functions and mathematical expressions**Mustard Yellow**for system, session, and presentation variables**Violet**for columns in the Data Panel-
**Red**for- Built-in function arguments
- Unidentified built-in functions
- Unidentified fully qualified named columns in the Data Panel
- Unidentified variable names.

#### Left and Right Gutters

The Formula Editor contains two gutters located on either side of the Formula Editor canvas.

The **Left Gutter** shows **line numbers** and allows for you to both collapse (**+**) and expand (**-**) parenthesized expressions when available.

The **Right Gutter** indicates the **cursor line** position with a horizontal line. In addition, the right gutter shows a grey bar to indicate a selected object name in the editor canvas and the beginning and end of parenthesized expressions.

### Footer Action Bar

In the **footer action bar**, select **Format** to format the formula expression in the Formula Editor canvas with automatic tab indentation.

The editor canvas shows vertical lines to detail the tab indentation positions.

Select **Cancel** to close Formula Builder without saving your changes.

**Validate & Save** verifies expression accuracy and will prevent a formula being saved, for example, if there is undefined field in an expression. An error message appears above the footer action bar with details about the validation error.

## Build a Formula Expression

To create a calculation in an insight, follow these steps:

- In the Data Panel of the Analyzer, select
**New Formula**. - Drag & drop the
**New Formula**to the**measure tray**. - In the pill’s Data properties, select the
**Formula text box**to open the Formula Builder. - Create the calculation in the
**Formula Editor**using data columns, built-in functions, and variables. To add a column, function, or variable, select the object and either drag and drop the object to the Formula Editor canvas or double click to add it to the editor. - Select
**Validate & Save**to apply your calculation and verify the expression accuracy - Provide a value for the Name property of the formula
- As needed, select the Format tab in the Properties menu, and the choose the appropriate data format for the calculation result such as Currency Rounded.
- In the Analyzer, select
**Done**in the action bar to save your changes.

### Create a Formula Filter on Measures

To create a Formula Filter for a measure:

- Drag and drop the New Formula button into the measures field.
- Select the down arrow corresponding to the new formula to define it.
- Set the aggregate function to “FORMULA”, in the “Aggregation” field. This causes the Analyzer to refer to the formula expression (entered in the next step) to determine the actual aggregation function.