Formulas allow you to create new columns of data that do not already exist in a dataset. Using the Formula Builder, you can segment data, calculate ratios, aggregate values, and build custom calculations directly within Chart Builder. After a formula is created and saved, it becomes available as a new column alongside other dataset fields, and CORE Insights calculates its values in real time whenever the formula is used in a chart or visualization.
Accessing the Formula Builder
The Formula Builder is accessed from within Chart Builder. Selecting the formula icon on the toolbar opens the Formulas dialog, which lists any formulas already created for the dataset across two tabs, Formulas and Aggregate Formulas. To create a new formula, select New Formula, which opens the Create Formula dialog.
Formula Modes
Every formula is written in one of two modes:
- Standardized Syntax uses a syntax that is not tied to a specific database, so formulas built this way work consistently across any connected data source. This is the recommended mode for most formulas and includes automatic error detection along with a Testing tab for validating results before saving.
- Data Engine Syntax (Advanced) uses syntax specific to the underlying database engine. This mode supports more advanced calculations but requires familiarity with the syntax of the connected data source.
Note: Switching between Formula Mode options while editing a formula displays a confirmation, since changing modes clears the current formula and requires it to be rewritten.
Creating a Formula in Standardized Syntax
- Select the Formulas icon on the toolbar, then select New Formula to open the Create Formula dialog.
- Select Standardized Syntax as the Formula Mode.
- Enter a name for the formula in the Formula Name box.
- Add a function to the formula by typing it directly in the Formula box, or by selecting a function under Insert Functions and selecting Add to Formula. The Insert Functions panel shows a preview of the function's description, parameters, and an example before it is added.
- Add a column to the formula by typing it directly in the Formula box, or by placing the cursor where the column should go and selecting a column under Insert Columns. Each column is listed along with its Type, either String or Numeric.
- Continue adding functions and columns until the formula is complete. The editor evaluates the formula as it is written and highlights syntax errors or incorrect data type usage. Hover over a highlighted error to view details.
- Once the formula is free of errors, test it using the Testing tab.
- Select Save to save the formula.
Testing a Formula in Standardized Syntax
- With a formula free of syntax errors, select the Testing tab.
- Review the values in the Input box, which lists the formula's columns as a JSON array along with sample values pulled from the dataset. Edit the values directly, or select Randomize Data to pull a new set of sample values.
- Select Test Formula. The result is displayed in the Output box, prefixed with Result:. If the test returns an error, select See Console Output to view the detailed error response from the database engine.
- Select Save once the formula returns the expected result.
Syntax Reference (Standardized Syntax)
Use the following reference when writing formulas in Standardized Syntax mode:
- Field/Column Syntax: Insert columns using the Insert Columns panel, by entering the column name directly, or by selecting them from the suggestion box. Columns are displayed as pills inside the formula.
- Function Syntax: Function names must be uppercase and followed by a bracketed argument list, for example ABS(25).
- Comment Syntax: Add a single line comment to a formula using //, for example // This is a comment.
- Operator Syntax: The following table lists the available operators. Normal operator precedence applies.
| Symbol | Operation |
| * | Multiplication of two numbers. |
| / | Division of two numbers. |
| + | Addition of two numbers. |
| - | Subtraction of two numbers, or negation of a number. |
| = | Tests equality of two values. |
| > | Tests if the first number is greater than the second number. |
| < | Tests if the first number is less than the second number. |
| >= | Tests if the first number is greater than or equal to the second number. |
| <= | Tests if the first number is less than or equal to the second number. |
| <> | Tests inequality of two values. |
| ( ) | Evaluates the bracketed expression before applying operators to it. |
Supported Values
Standardized Syntax formulas support the following data types:
- Numeric: Includes decimals and negative numbers.
- Strings: Any sequence of characters enclosed in quotes.
- Date/Time Values: Strings in formats such as MM/DD/YYYY or MM/DD/YYYY HH:MM:SS.
- Boolean: true or false values.
Aggregate Formulas
Aggregate Formulas are a special type of formula, created only in Standardized Syntax mode, that calculate a single result per group or category rather than per individual record. They are used as columns in charts that group data by category, such as bar or table charts, and are not supported in chart types that display data at the row level, such as Simple Tables or Dot Maps.
Key characteristics of Aggregate Formulas:
- Always return a numeric value.
- Cannot be nested within other aggregate functions.
- Calculate a single result per group or category, rather than per individual record.
- Operate directly on columns. Numeric and Logical functions can operate on columns included inside an aggregate function.
- Do not support text or date/time functions.
Standardized Syntax provides its own set of uppercase aggregate functions, such as SUM and AVG, that operate directly on a column. These are separate from the lowercase, agg_ prefixed aggregate functions available in Data Engine Syntax mode, such as agg_sum, which are written differently and used within that mode's syntax.
For example:
- This is not valid: [column1] + [column2]
- These are valid: SUM(column1), or SUM([column1]) + SUM([column2]) to combine two aggregates, or ROUND(SUM(column1)) to nest a numeric function around an aggregate, or IF(AVG(column1)>30, 0, 1) to nest a logical function around an aggregate.
Creating a Formula in Data Engine Syntax (Advanced)
- Select the Formula icon on the toolbar, then select New Formula to open the Create Formula dialog.
- Select Data Engine Syntax (Advanced) as the Formula Mode.
- Enter a name for the formula in the Formula Name box.
- Add a function to the formula by typing it directly in the Formula box, or by selecting a function under Insert Functions and selecting Add to Formula.
- Add a column to the formula by typing it directly in the Formula box, or by placing the cursor where the column should go and selecting a column under Insert Columns.
- Continue adding functions and columns until the formula is complete.
- Select Test to test the formula against random values from the dataset and review any errors that occur.
- Select Save to save the formula.
Data Engine Syntax Examples
You can use the dateFormat function to extract a value, such as the month, from a date, for example, to use as the category on a chart.
| Function | dateFormat |
| Syntax | dateFormat(date_value, 'format') |
| Example | dateFormat([orderDate], 'MM') |
You can calculate an aggregate across all rows in a column and use the result in a further calculation, for example, to calculate one project's share of total billed amount.
| Function | agg_sum |
| Syntax | agg_sum(value) |
| Example | [billedAmount] / agg_sum([billedAmount]) * 100 |
You can concatenate text columns using the + sign, for example to combine first and last name columns into a full name.
| Function | + |
| Syntax | value1 + value2 + value3... |
| Example | [lastName] + ', ' + [firstName] |
You can use an if statement to build more advanced logic, for example, to compare two date columns and return a different result depending on the outcome.
| Function | if |
| Syntax | if (condition) {return result1;} else {return result2;} |
| Example | if(dateDiff([orderDate], isNull([shippedDate], now()), 'DAYS') <= 3) {return 'fast';} else {return 'slow';} |
Syntax Reference (Data Engine Syntax)
- Field and Column Syntax: Use square brackets around the field or column name, for example, [Sales].
- Function Syntax: Function names must be lowercase and followed by a bracketed argument list, for example sum([Items]) or avg([Temp]).
- Operator Syntax: The following table lists the available operators. Normal operator precedence applies.
| Symbol | Operation |
| . | Applies the function following the dot to the string or text value before the dot. |
| * | Multiplication of two numbers. |
| / | Division of two numbers. |
| + | Addition of two numbers. |
| - | Subtraction of two numbers, or negation of a number. |
| == | Tests equality of two values. |
| > | Tests if the first value is greater than the second value. |
| < | Tests if the first value is less than the second value. |
| >= | Tests if the first value is greater than or equal to the second value. |
| <= | Tests if the first value is less than or equal to the second value. |
| != | Tests inequality of two values. |
| ^ | Bitwise exclusive or (XOR) of two values. |
| AND | Logical and of two values. |
| OR | Logical or of two values. |
| NOT | Logical not of a value. |
| ( ) | Evaluates the bracketed expression before applying operators to it. |
Display Dates and Times
Use the dateFormat function to control how a date or time value is displayed. For example, dateFormat("07/04/2001", "EEE, MMM d, ''yy") returns Wed, Jul 4, '01.
Note: Single or double quotes can be used around the format string. All dates are treated as GMT.
The following table lists the available formatting letters:
| Letter | Date or Time Component | Presentation | Example |
| G | Era designator | Text | AD |
| y, Y | Year | Year | 1996; 96 |
| M | Month in year | Month | July; Jul; 07 |
| w | Week in year | Number | 27 |
| W | Week in month | Number | 2 |
| D | Day in year | Number | 189 |
| d | Day in month | Number | 10 |
| F | Day of week in month | Number | 2 |
| E | Day name in week | Text | Tuesday; Tue |
| e | Day number of week (1 = Monday, ... 7 = Sunday) | Number | 1 |
| a | AM/PM marker | Text | PM |
| H | Hour in day (0-23) | Number | 0 |
| k | Hour in day (1-24) | Number | 24 |
| K | Hour in AM/PM (0-11) | Number | 0 |
| h | Hour in AM/PM (1-12) | Number | 12 |
| m | Minute in hour | Number | 30 |
| s | Second in minute | Number | 55 |
| S | Millisecond | Number | 978 |
| z | Time zone | General time zone | PST; GMT-08:00 |
| Z | Time zone | RFC 822 time zone | -0800 |
| x | Time zone | ISO 8601 time zone | -08; -0800; -08:00 |
Using Saved Formulas
When a formula is saved, it appears in the Data panel alongside the dataset's other columns and can be used the same way when building charts and filters.
For the full list of available functions in both Formula Modes, see CORE Insights formula functions list. For more on how formula columns fit into chart configuration, check Understanding Charts in CORE Insights help article for details.