Calculated fields

Purpose

Calculates new fields for every row. Formulas can use input columns, parameters from earlier blocks and fields already calculated above them in the list. The built-in ƒx wizard displays a function catalogue and examples.

Inputs and outputs

One in input; out contains the original and new fields. The row count does not change.

Parameters

Parameter	Settings and values	Options and limits
`fields`	From 1 to 100 fields: a unique name and an expression up to 2048 characters long.	Count: 1–100
`decimal`	The decimal separator for numeric values in the input data.	Options: `.`, `,` Default: `.`

Formula function catalogue

Function and arguments	Purpose	Example
`field(назва)`	The current row’s column value.	`field("Revenue")`
`param(назва)`	A parameter from an earlier block on the current branch only.	`param("Rate")`
`number(значення)`	Conversion to a number after the number-format block.	`number("12.5")`
`text(значення)`	Conversion of a value to text.	`text(125)`
`abs(число)`	The absolute value of a number.	`abs(-12)`
`round(число,знаків?)`	Round halves away from zero; precision from −15 to 15.	`round(12.345, 2)`
`min(числа*)`	The smallest of the supplied numbers.	`min(3, 8, 2)`
`max(числа*)`	The largest of the supplied numbers.	`max(3, 8, 2)`
`sum(числа*)`	The sum of arguments in the current row. To sum across rows, use grouping.	`sum(10, 20, 30)`
`average(числа*)`	The arithmetic mean of the arguments.	`average(10, 20)`
`ceil(число)`	Round towards positive infinity.	`ceil(-1.2)`
`floor(число)`	Round towards negative infinity.	`floor(-1.2)`
`trunc(число,знаків?)`	Discard the fractional part without rounding.	`trunc(12.349, 2)`
`roundup(число,знаків?)`	Round away from zero.	`roundup(12.341, 2)`
`rounddown(число,знаків?)`	Round towards zero.	`rounddown(-12.349, 2)`
`sqrt(число)`	The square root of a non-negative number.	`sqrt(144)`
`power(основа,степінь)`	Raise to a power (exponent from −1000 to 1000).	`power(2, 8)`
`mod(число,дільник)`	The remainder after division, with the divisor’s sign.	`mod(-7, 3)`
`sign(число)`	−1 for a negative number, 0 for zero, 1 for a positive number.	`sign(-12)`
`log(число,основа?)`	Logarithm; base 10 by default.	`log(1000, 10)`
`exp(число)`	The exponential e raised to the specified power.	`exp(1)`
`clamp(число,мінімум,максимум)`	Constrain a number to the specified interval.	`clamp(120, 0, 100)`
`percent(число,відсоток)`	The specified percentage of a number.	`percent(200, 15)`
`pi()`	The number π.	`pi()`
`concat(значення*)`	Concatenate text values.	`concat("Plan", "-", 2026)`
`upper(текст)`	Upper case.	`upper("hello")`
`lower(текст)`	Lower case.	`lower("HELLO")`
`title(текст)`	Capitalise every word.	`title("hello world")`
`trim(текст)`	Remove leading and trailing spaces.	`trim(" hello ")`
`length(текст)`	The number of Unicode characters.	`length("Київ")`
`left(текст,кількість)`	The first N characters.	`left("abcdef", 3)`
`right(текст,кількість)`	The last N characters.	`right("abcdef", 3)`
`mid(текст,позиція,кількість)`	A text fragment; the first position is 1.	`mid("abcdef", 2, 3)`
`replace(текст,старий,новий)`	Replace every occurrence of a substring.	`replace("a-b-c", "-", "/")`
`find(шукати,текст,позиція?)`	The case-sensitive substring position; 0 if not found.	`find("b", "abc")`
`search(шукати,текст,позиція?)`	The case-insensitive substring position; 0 if not found.	`search("B", "abc")`
`contains(текст,частина)`	Whether the text contains a substring.	`contains("abcdef", "cd")`
`starts_with(текст,початок)`	Whether the text starts with the specified substring.	`starts_with("ABC", "A")`
`ends_with(текст,кінець)`	Whether the text ends with the specified substring.	`ends_with("ABC", "C")`
`clean(текст)`	Remove control characters.	`clean("A\nB")`
`repeat(текст,кількість)`	Repeat text within a 32768-character limit.	`repeat("0", 3)`
`padleft(текст,довжина,символ?)`	Pad on the left with one character, a space by default.	`padleft("42", 5, "0")`
`padright(текст,довжина,символ?)`	Pad on the right with one character.	`padright("42", 5, "0")`
`transliterate(текст)`	Deterministic transliteration of Cyrillic/Greek text and removal of Latin diacritics.	`transliterate("Київ")`
`date(рік,місяць,день)`	Create a valid ISO calendar date.	`date(2026, 9, 27)`
`today()`	The current UTC date, fixed for the entire block.	`today()`
`now()`	The UTC time, fixed for the entire block.	`now()`
`datevalue(текст,формат?)`	Parse a date; ISO by default, or a strptime format.	`datevalue("27.09.2026", "%d.%m.%Y")`
`formatdate(дата,формат)`	Format a date; %Y — year, %m — month, %d — day.	`formatdate("2026-09-27", "%d.%m.%Y")`
`year(дата)`	The date’s year.	`year("2026-09-27")`
`month(дата)`	The date’s month from 1 to 12.	`month("2026-09-27")`
`day(дата)`	The day of the month.	`day("2026-09-27")`
`hour(датачас)`	The UTC hour from 0 to 23.	`hour("2026-09-27T12:30:00Z")`
`minute(датачас)`	The minute from 0 to 59.	`minute("2026-09-27T12:30:00Z")`
`second(датачас)`	The second from 0 to 59.	`second("2026-09-27T12:30:05Z")`
`weekday(дата)`	ISO weekday: Monday is 1, Sunday is 7.	`weekday("2026-09-27")`
`weeknum(дата)`	The ISO week number.	`weeknum("2026-09-27")`
`adddays(дата,днів)`	Add or subtract calendar days.	`adddays("2026-09-27", 7)`
`addmonths(дата,місяців)`	Shift by months; the day is limited to the end of the month.	`addmonths("2026-01-31", 1)`
`datediff(початок,кінець,одиниця?)`	Difference: days, months (complete months), years (complete years), hours, minutes, seconds.	`datediff("2026-01-01", "2026-02-01", "days")`
`eomonth(дата,зсув?)`	The last day of the current or shifted month.	`eomonth("2026-01-15", 1)`
`coalesce(значення*)`	The first value that is neither null nor empty.	`coalesce(None, "", 0, 5)`
`ifelse(умова,так,ні)`	A lazy condition: only the selected branch is evaluated.	`ifelse(10 > 5, "yes", "no")`
`iferror(вираз,заміна)`	Replace a calculation error; syntax errors and missing fields are not hidden.	`iferror(10 / 0, 0)`
`isblank(значення)`	Whether a value is null or empty text.	`isblank("")`
`isnumber(значення)`	Whether a value is a number rather than text or a Boolean.	`isnumber(12.5)`
`istext(значення)`	Whether a value is text.	`istext("12")`
`and_(умови*)`	All conditions are true; lazy evaluation.	`and_(1 > 0, 2 > 1)`
`or_(умови*)`	At least one condition is true; lazy evaluation.	`or_(False, True)`
`not_(умова)`	Logical negation.	`not_(False)`

Step-by-step configuration

Add a field and give it a new name.
Insert a reference using field("Name") or use the field list. Specify a parameter with param("Name").
Choose a function in the ƒx wizard, fill in its arguments and check the formula.
Place dependent fields below the fields they use.
Check zero, empty and invalid values in a simulation.

Rules and diagnostics

Existing columns are not overwritten; use “Set field values” to replace them.
Use a decimal point in formulas and put text in quotes. The +, −, *, / and % operations work with numbers; use concat to join text.
A formula cannot reference a field that has not yet been calculated. SOURCE_COLUMNS_MISSING and PARAMETER_UNKNOWN indicate unavailable names.
ifelse evaluates only the selected branch. For division, check for zero or use iferror for expected calculation errors.
Text results are limited to 32768 characters; split an overly complex formula into several fields. Current-time functions use one UTC instant for the entire block.

Configuration example

Create Amount = field("Price") * field("Quantity"), then AmountUAH = round(field("Amount") * param("Rate"), 2). For the average price, use ifelse(field("Quantity") == 0, 0, field("Amount") / field("Quantity")).

Formula editor

The editor highlights formulas, suggests functions, fields and parameters while typing, and shows the current argument hint. Open a function’s detailed description for argument explanations and an evaluated example. “Full screen” opens a large editor with field, parameter and function lists; “Collapse” returns to the panel. Multiline expressions and # or // line comments are supported. Ctrl/⌘ + / toggles comments on selected lines; comments do not count toward the 2,048-character expression limit.

Removing text from either end

removeleft(text, count) returns text without its first N characters; removeright(text, count) removes its last N characters. The remaining text is unchanged. For example, removeleft("UA-001", 3) yields 001, and removeright("report.csv", 4) yields report. This differs from left and right, which keep the corresponding fragment.