How-to

How to create calculated fields with formulas in Hubviz

Create a new cube field from a formula, such as gross profit, margin or target status, and test the result on every row before you save.

Create a new column in the cube from a formula over the other fields, without touching the data source. Example: receita - custo becomes the Gross profit field.

When to use it

To compute a value row by row, such as profit, margin or an order’s total value.
To classify records, such as “Target met” and “Below target”, or late-payment bands.
To build text or a date from other fields, such as “Q1/2026” from the date.

The formula runs on each row of the cube, looking only at the fields of that row. It doesn’t sum or average across rows. For that, use measures on top of the calculated field.

Before you start, choose the cube fields on the Field Mapping tab. See Cube fields.

Create the field

In the project side menu, open Cubes and click the cube.
Open the Field Mapping tab and click Add Calculated Field. The New Calculated Field window opens.
Fill in User Friendly Name, such as “Gross profit”. The technical name (gross_profit) is created from it.
If you want, explain the calculation in Description.
Click the formula editor (Construtor de Fórmulas in the product, the Formula Builder) and write the formula. When you click the editor, a panel with fields and functions opens beside it. Click an item to insert it. You can also type and pick from the suggestion list.
Check the Resultado (Result) line right below the editor. It uses the first row of the cube data.
To test on every row, click the speedometer icon in the example data section (Test formula on all rows). If any row fails, the Formula Errors button shows up at the bottom left.
Click Save.
Adjust the field type in the table and save the cube with Ctrl + S (see the next section).

Adjust the type after saving

The calculated field joins the cube checked, in the table of the Field Mapping tab. It starts with the Value type and the Text format, whatever the formula. Change them:

Formula result	Field type	Value format
Number (profit, quantity)	Value	Number, Decimal or Currency
Text (status, band)	Category	Text
Date	Date	Date

To edit or delete the field, use Edit Calculated Field and Delete Calculated Field in the Actions column of its row.

How to write the formula

Element	How to write it	Example
Field	The technical name from the Field Name column of the mapping table, with no quotes	`receita`
Number	With a decimal point	`0.15`
Fixed text	In single quotes	`'Target met'`
Math	`+`, `-`, `*`, `/` and parentheses	`(receita - custo) / receita`
Comparisons	`==`, `!=`, `>`, `<`, `>=`, `<=`	`receita >= meta`
Functions	Name in capitals, arguments in parentheses, separated by commas	`ROUND(receita, 2)`

Use == to compare, as the editor suggests.
To combine conditions, use AND(...), OR(...) and NOT(...).
To insert a field without mistyping its name, click it in the Campos panel.
When the cube is processed, an empty field counts as 0, and text that looks like a number ("150.50") is read as a number.

Functions

Functions live in the Matemática, Texto, Data and Lógica tabs of the panel. When you type a name, the editor shows the parameters.

Formula builder function panel on the Lógica tab, with the cards IF, IFS, IFERROR, IFEMPTY, SWITCH and AND and a description of each

Group	Function	What it does
Logic	`IF(condition, if_true, if_false)`	Picks between two values.
Logic	`IFS(cond1, value1, cond2, value2, ...)`	Several conditions in sequence. End with `true, value` for the remaining case.
Logic	`IFERROR(value, value_if_error)`	Replaces an error or a division by zero with another value.
Logic	`IFEMPTY(value, value_if_empty)`	Replaces empty text with another value.
Logic	`SWITCH(expression, case1, res1, ..., default)`	Compares with each case. With no matching case and no default, it errors.
Logic	`AND(...)`, `OR(...)`, `NOT(value)`	Combine conditions.
Math	`AVG(n1, n2, ...)`, `MAX(...)`, `MIN(...)`	Average, largest and smallest among the values in this row.
Math	`ROUND(n, places)`	Rounds. Without `places`, rounds to an integer.
Math	`CEIL(n)`, `FLOOR(n)`, `INT(n)`, `ABS(n)`	Round up, round down, drop the decimal part, absolute value.
Math	`RANDBETWEEN(low, high)`	Random integer. It changes at every processing.
Math	`VALUE(text)`	Converts text to a number. Accepts a decimal comma.
Text	`CONCAT(t1, t2, ...)`	Joins text and numbers.
Text	`LEFT(text, n)`, `RIGHT(text, n)`, `MID(text, start, n)`	A piece of the text. The start position of `MID` begins at 1.
Text	`UPPER(text)`, `LOWER(text)`, `TRIM(text)`, `LEN(text)`	Uppercase, lowercase, trim the ends, count characters.
Text	`REPLACE(text, old, new)`	Replaces every occurrence.
Text	`SUBFIELD(text, separator, position)`	A part of the text split by the separator. The position begins at 1.
Date	`DATE(year, month, day)`	Builds a date.
Date	`PARSE_DATE(text, format)`	Converts text to a date. The format is optional, such as `'dd/MM/yyyy'`.
Date	`TODAY()`, `NOW()`	Today’s date, and the current date and time.
Date	`ADD_DAYS(date, n)`, `ADD_MONTHS(date, n)`, `ADD_YEARS(date, n)`	Adds days, months or years. Use a negative number to subtract.
Date	`DAYS_BETWEEN(d1, d2)`, `MONTHS_BETWEEN(d1, d2)`, `YEARS_BETWEEN(d1, d2)`	Difference between two dates.
Date	`YEAR(date)`, `MONTH(date)`, `DAY(date)`, `HOUR(date)`, `MINUTE(date)`	A part of the date.
Date	`WEEKDAY(date)`, `WEEKNUMBER(date)`	Day of the week (1 is Monday, 7 is Sunday) and week number.
Date	`DATE_ONLY(date, format)`	The date as text, without the time. The format is optional and defaults to `dd/MM/yyyy`.

Examples with the Sales cube

Calculated field	Formula	Result on the first row	Type
Gross profit	`receita - custo`	499451	Value, Number
Margin (%)	`ROUND((receita - custo) / receita * 100, 1)`	32.7	Value, Decimal
Target status	`IF(receita >= meta, 'Target met', 'Below target')`	Below target	Category, Text
Quarter	`CONCAT('Q', CEIL(MONTH(data) / 3), '/', YEAR(data))`	Q1/2026	Category, Text
Safe margin	`IFERROR((receita - custo) / receita, 0)`	0.327	Value, Decimal

A banding with IFS:

IFS(dias_atraso > 60, 'Critical', dias_atraso > 30, 'Warning', true, 'On time')

Test the formula

The example data sits in a section below the editor, with row 1 of the cube.

Control	What it does
Resultado line	Shows the formula’s value for the example row, or the error message.
Refresh icon	Fetches the example data from the source again.
Speedometer icon, Test formula on all rows	Runs the formula on every row read and shows each result, such as `Linha 1: 499451`.
Example data	The JSON is editable. Change a value to see how the formula reacts to a specific case.
Formula Errors	Lists the rows that failed, with the message and the variables used. Shows up to 200 rows. Click one to see that row’s data.

New Calculated Field window with the formula receita - cust, the Resultado in red saying Undefined symbol cust and the Formula Errors button with the number 1 at the bottom left

Messages and limits

Message	Cause and fix
Please fill in all required fields.	The User Friendly Name is empty.
The formula cannot be empty.	Write a formula before saving.
Waiting for data to load…	The editor has no example data yet. Check that the cube read the source, or click the refresh icon.
Undefined symbol …	The formula names something that is neither a field nor a function. Check the name in the Field Name column.
Fórmula inválida: …	Shows up when the cube is processed (the text is in Portuguese in the product). The formula has a syntax error, such as an unclosed parenthesis.
No query to execute.	The cube has no connection yet. The editor needs one to fetch the example.
Are you sure you want to exit without saving changes?	You closed the window with unsaved changes.

A calculated field only sees the fields of its own cube, including relationship fields.
Use IFS with true at the end. Without it, rows that match no condition stay empty.

Next steps

Adjust the new field’s type in Cube fields.
Summarize the field with measures, such as the sum of Gross profit per month.