Calculated fields
A calculated field is an additional data field with values calculated using a formula.
You can use calculated fields to create new dimensions and measures.
The data source remains unchanged.
To write formulas, you can use existing dataset fields, constants, and functions.
You can create calculated fields in the dataset or wizard interface:
- Calculated fields from a dataset are available in all charts above this dataset.
- Calculated fields from a wizard are only available for the saved chart.
Calculated fields in datasets
You can add new fields to the list of dataset fields.
When creating a formula, you can use any dataset field, including the calculated fields that you created earlier.
After creating a calculated field and saving the dataset, the field becomes available to all the charts and dashboard selectors based on that dataset.
To prevent fields from being displayed in the wizard, enable the Don't display option when creating them.
Calculated fields are marked with .
Calculated fields in charts
You can add new fields when creating and editing charts in the wizard:
- In the list of fields from a dataset.
- In the visualization section.
When creating a formula, you can use any dataset field, including the calculated fields that you created earlier.
If a field is created in a chart, it isn't available to dashboard selectors and other charts.
Calculated fields are marked with .
Warning
In multi-dataset charts, calculated fields are not applicable to fields from multiple datasets.
Functions
Functions are the main components used for creating formulas. You can use them to perform various operations on data fields.
The list of available functions depends on the data source. For more information, see Function Availability.
Warning
Avoid calculation loops: in a formula, you can't use a field that uses the same formula to calculate its own value.
Notation format
Write formulas that meet the following requirements:
- Function arguments are specified in parentheses. For example,
YEAR([DATE1]). Multiple arguments are separated by a comma. - Field names are given in square brackets. For example,
[CustomerID]. - Make sure to enter the values of fields and constants in a valid format. For example, use a dot as a decimal separator:
0.123.
Note
The function syntax is case-insensitive: count and COUNT perform the same operation.
For reference information about the format, types of accepted arguments, and returned function values, see the formula editor interface.
To do this, click Reference in the add field interface.
Examples of using functions
Concatenation of string values
[City] + " " + "city"
Concatenates the following three string values: the [City] field value, the space, and the city text. For concatenation, you can also use the CONCAT string function.
Full description of the addition operator (+).
Truncation of dates
DATETRUNC(#2018-07-12#, "year", 5) = #2015-01-01#
Truncates the date 2018-07-12 to a year that is a multiple of 5.
Full description of the date truncation function DATETRUNC.
Type conversion
FLOAT([StringWithNumbers])
Converts the StringWithNumbers string field to a fractional number.
Full description of the FLOAT conversion function.
How to create a calculated field
You can create calculated fields in the dataset or wizard interface.
- Open the dataset.
- In the upper-right corner, click Add field.
- Enter a name for the field.
- Select Formula as the data source for the field.
- Set the formula using data source functions.
- Click Create. The field appears in the dataset.
- Open the wizard.
- Select the dataset to create a chart in.
- To the left of the screen, click above the list of dataset fields.
- Enter the required formula.
- Click Create. The field appears in the list of fields. You can use it as your chart's data source.