Calculations

Aggregate list

Turns a whole list into one number — the total of a column, its average, the smallest, the largest, or how many entries there are.

When to use it: after Iterate rows, to get the figure the workflow needs: the total of a column of amounts, the average of a column, the number of rows. Calculate variable works with two values; this one works with all of them at once.

It adds up a column, not a figure worked out row by row. Field names a column each entry already carries. An invoice line —quantity × price— is not one of them: work it out inside the For each with Calculate variable and add it to a running total there, or write it into each entry first with Modify element field and aggregate afterwards.

It works with every format. Iterate rows leaves the rows of a spreadsheet, a CSV, a document or Markdown table, a presentation table or a text file in the same shape, so the same instruction adds up all of them and there is no “sum a column” per format.

Important behavior: with no Save key, or when List names something that is not a list, nothing is saved and the run says so. sum, average, min and max only look at the entries that are numbers, and step over blanks and words, which is what a spreadsheet does with a column. When not one entry is a number — a Field nobody has, or a column of text — nothing is saved and the run says which name it read: a total that was never worked out must not arrive as 0. count counts entries, all of them, whatever they hold, and does not look at Field. Sums are exact in base 10, so 0.1 + 0.2 is 0.3.

A spreadsheet formula is a different thing. Writing =SUM(D2:D11) into the file with Write math formula is better for the file — it recalculates itself when someone edits the sheet. But the engine does not work it out, the spreadsheet program does when the file is opened, so that total is not available to the workflow: to compare it, print it in another document or use it in a file name, add it up here.

Calculate variable

Does one operation with two values and saves the result.

When to use it: to work out an amount, a percentage or a total — the line of an invoice (quantity × price), a running total inside a For each, a discount.

Important behavior: with no Save key the instruction does nothing. percentOf is the second value per cent of the first (340 and 21 gives 71.4); percentage is how much per cent the first is of the second (34 and 340 gives 10). intDivide gives the quotient with the decimals cut off. power with a whole exponent is exact (1.1 to the 3 is exactly 1.331). Rounding goes away from zero at the midpoint: 2.5 with 0 decimals is 3. The result is saved with no trailing zeros: 4 × 45.5 is saved as 182.

Fit text to width

Makes a piece of text exactly as wide as you say, filling what is left over so the next one starts in the same place on every line.

When to use it: to make columns in a plain text file — a delivery note, a listing, a receipt — where the columns are made of spaces and nothing else. Amounts read right-aligned, names left-aligned. It is also what draws a line of dots between a heading and its page number, using . as the fill.

Important behavior: it saves text, so it goes wherever text goes and works for all eight formats — but it only lines anything up where every character takes the same space, which is the plain text file. A text longer than Width comes out whole and pushes its own column: losing part of a value has to be asked for with Cut if it does not fit. When centring leaves an odd space over, the extra one goes on the right. The natural order is Calculate variable → Format number → this one: work the number out, write it the way a person reads it, then line it up.

Format number

Writes a number the way a person reads it — 1,234.56, 1.234,56 €, 0007 — and saves it as text.

When to use it: as the last step, once the number is worked out and just before it goes into the document: an amount with its currency, a percentage, an invoice number padded with zeros.

Important behavior: with no Save key, or with no number to show, nothing is saved. The result is text, so it goes wherever a text goes —a paragraph, a cell, a slide, a JSON field— but the calculations can no longer read it back as a number: this is the last step, not the first. The symbol is glued in front ($1,234.56) and takes a space when it comes after (1.234,56 €), and the minus sign stays outside it (-$1,234.56). Rounding goes away from zero at the midpoint, so 2.345 with 2 decimals is 2.35. Zero is not positive, so it never gets a +. The zeros added by Minimum digits are grouped like any other digit (0,007 in the english style), so a code that must not be grouped asks for the plain style.

Group and aggregate

One number per group — the total per salesperson, the average per region, how many rows each category has. It splits a saved list into groups by the value of one field and collapses each group into a single figure.

When to use it: after Iterate rows, when the report has one line per group instead of one line per row. Aggregate list turns a whole list into one number; this one turns it into one number per group, which is what a summary is.

What it saves is a list you can walk. The result is a list with one entry per group, each carrying the group’s name and its value, so a Repeat for each over it writes the report: the current entry’s @group.name is the heading and @group.value is the figure. Glue the two into a row with Set text, because a @name only resolves when it is the whole value.

It works with every format. Iterate rows leaves the rows of a spreadsheet, a CSV, a document or Markdown table, a presentation table or a text file in the same shape, so the same instruction groups all of them and there is no “group by” per format. If the data is already an open CSV, Group and aggregate (.csv) does it in one step and writes the table into the document itself.

Important behavior: the groups come out in the order they first appear in the list, so the report follows the data and not the alphabet. With no Save key and no Save count key, or when List names something that is not a list, or with no Group by field, nothing is saved and the run says so. Entries that are not records, or that have no such column, are grouped together under _unknown, which you can see in the result. sum, average, min and max only look at the entries that are numbers; a group where not one of them is keeps its line with an empty figure — the group is not dropped and a total nobody worked out does not arrive as 0 — and the run says which group it was. count counts entries, whatever they hold, and does not look at Field. Sums are exact in base 10.

List fields

Says which columns a list has.

When to use it: right after opening a file and running Iterate rows, to check that the column you are about to read really is there — or to walk the columns with a For each.

Important behavior: with both save fields empty, or when List names something that is not a list, nothing is saved and the run says so. The columns come out in the order they appear, and the whole list is walked, so a column carried by only some entries also shows up. Without this, “the amounts are in Importe” is a guess: if the title does not match, the sum comes out at zero and nothing warns you.

Round variable

Rounds a number or takes its whole part.

When to use it: to leave an amount at two decimals, to round a quantity up, or to drop the sign.

Important behavior: round goes away from zero at the midpoint (2.5 gives 3, -2.5 gives -3), which is what a spreadsheet does. floor, ceiling and truncate also work at the decimal place given in Decimals, so they can round to cents and not only to units. truncate cuts towards zero. Decimals limits how many decimals are kept; it does not add zeros to reach them, which is a question of presentation and not of calculation.

Scientific calculation

Roots, logarithms and angles on a value, saved under a name.

When to use it: whenever the calculation is not one of the four basic ones — a square root, a logarithm, the sine of an angle.

Important behavior: angles are in radians, like a spreadsheet — toRadians and toDegrees are in the list for whoever thinks in degrees. Nothing is saved when the calculation has no answer: the root of a negative number, the logarithm of zero or less, a logarithm in base 1, an arc sine or arc cosine outside -1 to 1. Results are kept to 15 significant digits.