Documentation

Formula Reference

Complete guide to writing calculated fields, conditional logic, aggregations, and supply chain functions using Context API and Fn helpers.

← Back to Documentation Hub / Building Blocks

Formula Reference

Custom formulas in reports combine three primary building blocks:

  1. Row Data: row['Column_Name'] pulls the specific value from the current data row.
  2. Context API (API): Fetches report-level information, run dates, or environment lookup properties.
  3. Helper Functions (Fn): Converts values, applies logical conditions, runs aggregations, or executes supply chain algorithms.

Combine these components using standard arithmetic operators (+, -, *, /) or string joining operations.


Context API Reference (API)

The API object provides execution environment details and structural lookup utilities.

  • API.look(path, data, default=None, astype=None): Searches structured JSON or dictionary structures using JMESPath expressions. Optional astype converts output to 'str', 'int', or 'float'.
  • API.dt(datelike=None, tz=None, format=None, default=None): Normalizes date inputs into a standardized report date object. Returns current execution time when no input is provided.
  • API.isoweek(shift=0, unit='days', now=None, base=None): Calculates the ISO calendar week identifier formatted as YYYY-00-WW.

Type Conversion & Math Utilities (Fn)

Type Conversions

  • Fn.str(a): Converts any input value to a text string (e.g., Fn.str(row['account_code'])).
  • Fn.int(a): Converts numeric text or decimals into integers, setting missing or null values to 0 (e.g., Fn.int(row['quantity']) + 1).
  • Fn.float(a): Converts values to decimal numbers, setting missing values to 0.0 (e.g., Fn.float(row['tax_rate']) * 100).

Basic Math & Comparison

  • Fn.plus(a, b): Adds two numbers or joins two text strings safely (defaults missing values to 0).
  • Fn.minus(a, b): Subtracts b from a safely.
  • Fn.mult(a, b): Multiplies a by b or repeats string a b times.
  • Fn.div(a, b): Divides a by b safely, returning 0 if dividing by zero.
  • Fn.abs(a): Returns the absolute positive magnitude of a number.
  • Fn.ceil(a, base=0) / Fn.floor(a, base=0): Rounds values up or down to whole numbers or nearest base multiples.
  • Fn.round(a, b=None): Rounds value a to b decimal places.
  • Fn.min(a, b) / Fn.max(a, b): Returns the smaller or larger value between two numbers.

Logical Comparisons

Evaluate expressions and return True or False:

  • Fn.eq(a, b): Equal to (a == b)
  • Fn.ne(a, b): Not equal to (a != b)
  • Fn.gt(a, b) / Fn.ge(a, b): Greater than / Greater than or equal to
  • Fn.lt(a, b) / Fn.le(a, b): Less than / Less than or equal to

Column Aggregations & Grouping

Aggregation functions summarize values across dataset rows. Most support an optional groupby parameter (e.g., groupby=['region']) to calculate grouped sub-totals:

  • Fn.Sum(a, groupby=None): Sums column values (e.g., Fn.Sum(row['sales_amount'], groupby=['region'])).
  • Fn.Avg(a, groupby=None): Computes the arithmetic average of numeric values.
  • Fn.Min(a, groupby=None) / Fn.Max(a, groupby=None): Extracts lowest or highest values within a grouping.
  • Fn.Median(a, groupby=None) / Fn.Mode(a, groupby=None): Finds middle value or most frequent entry.
  • Fn.Count(a, groupby=None): Counts non-empty rows.
  • Fn.CountDistinct(a, groupby=None): Counts unique non-repeating entries (e.g., Fn.CountDistinct(row['customer_id'], groupby=['country'])).
  • Fn.StdDev(a, groupby=None): Measures standard deviation across dataset values.
  • Fn.StringConcat(a, groupby=None) / Fn.UniqueList(a, groupby=None): Combines row text into full or deduplicated lists.
  • Fn.TakeFirst(a, groupby=None): Extracts the first recorded entry in a dataset group.
  • Fn.CumSum(a, groupby=None): Computes running cumulative totals across sequential rows.
  • Fn.Quantile(a, q): Returns the value marking percentile cutoff q (0.0 to 1.0).
  • Fn.aggregateif(to_sum, criterion, value, aggregator='Sum'): Conditionally aggregates values matching specific filter criteria.

Domain & Supply Chain Helpers

  • Fn.use(a): Passthrough function returning value a unchanged, useful for corporate rule macros.
  • Fn.when(test, true_value, false_value): Returns true_value when test condition is True, otherwise returns false_value (e.g., Fn.when(row['sales'] > 10000, "Enterprise", "Standard")).
  • Fn.rename(a, mapper, default=None): Maps input key a using a translation dictionary.
  • Fn.look(expression, data, default=None): Queries nested JSON objects using JMESPath syntax.
  • Fn.timediff(a, b): Computes time duration between two date parameters.
  • Fn.forward_need(cover_weeks, forecasts): Evaluates future demand forecasts to compute stock purchase requirements needed to cover target weeks of inventory.
  • Fn.transfers(col_product, col_location, col_locations, col_levels, col_need): Calculates inventory rebalancing recommendations across distribution hubs based on stock levels and destination shortfall needs.
  • Fn.forecast(values, weeks, length, single_value=True): Uses historical weekly sales performance to project future period demand via the Forecasting Engine.
  • Fn.batch_aging(batches, quantities, dates): Computes stock aging distribution and lot depletion tracking.

Structural & Dataset Mutators

Mutators reshape table structure, order, and row counts across entire report datasets:

  • Fn.Sort(key, asc=True): Re-orders dataset rows by target column key in ascending or descending order.
  • Fn.Reduce(by, reducer='Count', reducers=None): Aggregates full datasets by specified dimensions.
  • Fn.Where(query, default=None): Filters report datasets, returning only rows matching conditional query strings.
  • Fn.AggregateWhere(a, query, aggregator='Sum'): Filters data by criteria and executes targeted aggregations over matching subsets.
  • Fn.Subtotal(on, aggregators, label='Subtotal'): Automatically inserts formatted subtotal summary rows whenever grouping column values change.
  • Fn.Unpack(column, sep=None, missing=None): Expands delimited string lists into standalone dataset rows.
  • Fn.Take(take, skip=0): Extracts specific row ranges for dataset sampling or pagination.
  • Fn.AddRows(rows, fields=None): Appends empty or pre-filled placeholder rows to existing datasets.
  • Fn.RollingSum(column, window, groupby=None): Computes moving cumulative totals over defined window frames.

← Back to Documentation Hub / Building Blocks