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:
- Row Data:
row['Column_Name']pulls the specific value from the current data row. - Context API (
API): Fetches report-level information, run dates, or environment lookup properties. - 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. Optionalastypeconverts 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 asYYYY-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 to0(e.g.,Fn.int(row['quantity']) + 1).Fn.float(a): Converts values to decimal numbers, setting missing values to0.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 to0).Fn.minus(a, b): Subtractsbfromasafely.Fn.mult(a, b): Multipliesabybor repeats stringabtimes.Fn.div(a, b): Dividesabybsafely, returning0if 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 valueatobdecimal 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 toFn.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 cutoffq(0.0to1.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 valueaunchanged, useful for corporate rule macros.Fn.when(test, true_value, false_value): Returnstrue_valuewhen test condition isTrue, otherwise returnsfalse_value(e.g.,Fn.when(row['sales'] > 10000, "Enterprise", "Standard")).Fn.rename(a, mapper, default=None): Maps input keyausing 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.