Calculate with formulas, running totals and ranks

Write the calculation in SQL mode

1. Click Describe what you want to generate and type what you want in plain words, for example "revenue after a 15% discount".
2. Click Generate. Mammoth writes a SQL expression into the SQL Expression editor. Review or edit it, or type SQL there yourself.
3. If your description also says which rows to change ("double the price where region is Europe"), Mammoth puts that part into the step's row condition instead of the expression. Check it with the filter icon before you apply.
4. To add a parameter, click Insert Parameter. It goes in at the cursor as {Discount_Rate}. Text and Date parameters are wrapped in single quotes ('{Region}') so they read as SQL strings.

Example: ROUND("unit_price" * "quantity_sold" * (1 - {Discount_Rate}), 2) uses a Number parameter and DuckDB's ROUND function.

Write the calculation in Formula mode

Type a formula with column names in double quotes, the operators +, -, * and /, brackets and the built-in functions. The placeholder shows the shape: "Column1" + "Column2" or SUM("Column1") + INT("Column1"/10). An autocomplete list appears as you type: pick a column, function or parameter to insert it. A message under the formula says "Looks good" or names the problem.

Examples:

Calculate only for rows that match a condition

Click the filter icon at the left of the Place in row. The condition dialog has the same AI prompt, Builder and SQL options as Conditional Filter. Rows that don't match keep their current value when you write to an existing column. In a new column they stay empty.

Formula syntax and built-in functions

A formula can start with - or + but not with * or /. An empty cell in a column counts as 0. Dividing by 0 leaves the result cell empty.

SUM, AVG, MIN, MAX, STDDEV and COUNT look at the whole column, not the current row, so every row gets the same value. If the step has a condition built in Builder, they cover only the matching rows. INT and ABS take a full formula, so they can wrap other functions: INT(SUM("units") / COUNT()).

Use a parameter in a formula

Click Insert Parameter above the formula, or pick the parameter from the autocomplete list. Only Number parameters can be used in a formula. Each run uses the parameter's current value. When the value changes, the pipeline is marked stale until you rerun it. See Parameters and snippets.

Warnings about IDs and money

In Formula mode, Mammoth adds a note when a formula does arithmetic on a column marked as an ID ("IDs are labels, not amounts, so doing arithmetic on them isn't meaningful.") or mixes an amount of money with a percentage ("This formula mixes an amount of money with a percentage. Check that the result is what you mean."). The notes never block the step. See Column types, IDs, currency and percentages.

Write SQL expressions in DuckDB SQL

Math Function's SQL mode and Conditional Filter's SQL mode take a SQL expression instead of a formula. Pipeline steps run on DuckDB, so write DuckDB SQL and use any DuckDB function, such as ROUND, COALESCE, CASE WHEN or DATE_DIFF.

• Put column names in double quotes and text values in single quotes.
• Describe what you want to generate writes the SQL from a plain-English description. Edit the result before you apply.
• In Math Function, Insert Parameter adds a parameter as {Discount_Rate}, with single quotes around Text and Date parameters. A Conditional Filter's SQL doesn't read parameters; use Builder mode with the Parameter comparison type there.
• Math Function's SQL result is always stored as a number.

For a whole query across a View, use Write SQL with AI.

Add a running total, rank or moving comparison

Window function runs a calculation across rows and keeps every row. Group & Pivot gives one summary row per group; a window function adds its result as a new column beside the original rows. Use it for rankings, running totals and row-to-row comparisons. A number column's 3-dot menu has shortcuts: Calculate running total and Ranking.

Mammoth warns when a sum, average or ranking runs over an ID column: "IDs are labels, not amounts, so a running total or ranking of them isn't meaningful." The warning doesn't block Apply.

Rank customers by revenue within each region

1. Open Window function and select Rank.
2. Click Group by and select region.
3. Click Add Sort, choose revenue and sort descending.
4. Name the new column and click Apply.

Every customer row gets a rank within its region, not across the whole dataset.

Find the largest or smallest of several columns in each row

Obtain Large or Small Values compares a set of number columns row by row and returns the Nth largest or smallest value.

1. In Select columns, tick the number columns to compare (at least two). Use the search box to find them.
2. In Select Nth, set the rank to return: 1st, 2nd, 3rd and so on.
3. Choose Largest value or Smallest value.
4. Optional: in Add a custom value to compare, add a fixed number. It counts as an extra column and shows as its own column in the preview. It can be a Number parameter (click the Val chip).
5. Check the Preview, which updates as you go.
6. In Place in, name the output column or choose an existing column to overwrite. Click Apply.

For example, with three price columns, supplier_a_price, supplier_b_price and supplier_c_price, select all three, choose 1st and Smallest value. The result is the cheapest quote in each row, so you can see the best supplier per product.

Troubleshooting formula messages

The formula is checked as you type. A valid formula shows "Looks good". Otherwise the message names the problem:

In the formula's column list, a column can be marked "(column missing)" or "(not a numeric column)".

Other problems:

Related

• Work with dates and financial years: the Date Tools in the same category.
• Work with text and JSON: the Text Tools in the same category.
• Group, pivot and unpivot: one summary row per group instead of a window.
• Write SQL with AI: run a whole SQL query on a View.
• Filter rows: SQL conditions for keeping or removing rows.
• Parameters and snippets: values you reuse across formulas.