Calculable fields
A calculable field is an additional data field with values calculated using a formula.
You can use calculable fields to create new dimensions and measures.
The data source remains unchanged.
To write formulas, you can use the existing dataset fields, parameters, constants, and functions.
You can create calculable fields in the dataset or wizard interface:
- Calculable fields from a dataset are available in all charts above this dataset.
- Calculable fields from the wizard are only available for saved charts.
Calculable fields in datasets
You can add new fields to a dataset field list.
When creating a formula, you can use any dataset field, including the calculable fields you previously created.
After creating a calculable field and saving the dataset, the field becomes available to all charts and dashboard selectors based on that dataset.
To hide fields in the wizard, enable the Don't display option when creating them.
Calculable fields are marked with .
Calculable 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 calculable fields you previously created.
If a field is created in a chart, it will not be available to dashboard selectors and other charts.
Calculable fields are marked with .
Warning
In multi-dataset charts, calculable fields are not applicable to fields from multiple datasets.
Functions
Functions are the principal 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 cannot use a field that uses the same formula to calculate its own value.
Notation format
Write formulas that meet the following requirements:
- Specify function arguments in parentheses, e.g.,
YEAR([DATE1]). Separate multiple arguments with commas. - Specify field names in square brackets, such as
[CustomerID]. - Make sure to enter the values of fields and constants in a valid format. For example, use a period as a decimal separator:
0.123.
Note
- Field name syntax is case-sensitive. For example, if a formula uses
[NAME], and a dataset contains only[Name], the formula field will be highlighted in red. - Function syntax is not case-sensitive. For instance,
countandCOUNTwill perform the same operation.
For more information on format, types of accepted arguments, and returned function values, use the Reference button when adding a field in the formula editor interface.
How to create a calculable field
You can create calculable fields in the dataset or wizard interface.
- In the left-hand panel, click Datasets and select the dataset you need. If you do not have a dataset, create one.
- In the top-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 will appear in the dataset.
- Open the wizard.
- Select the dataset to create a chart in.
- In the left part of the screen, click above the list of dataset fields.
- Enter the required formula.
- Click Create. The field will appear in the list of fields. You can use it as your chart data source.