Dataseries expressions

How to write the formula of a calculated series in Axmo — references, filters, arithmetic, aggregation, conditions and missing values.

A dataseries expression is the formula of a calculated series. It is what you write in the Series Editor's formula box, and it is evaluated once at every coordinate the series covers.

A dimensional expression says where to look. A dataseries expression says what to work out.

The two are used together constantly — a filter is a dimensional expression sitting inside a dataseries one — but they are different notations doing different jobs, and it is worth keeping the difference in mind when a formula does not behave.

Referring to another series

Write the name.

Profit = Revenue - Total Cost
Margin = Profit / Revenue

That is the whole of it. Names are case-insensitive, spaces are ordinary characters, and a reference to another series is read at the same coordinate the formula is being evaluated at — so Revenue - Total Cost means this month's revenue less this month's cost, in every month, without your writing anything to say so.

A series whose name carries punctuation an ordinary name cannot hold is written in square brackets — [Profit Contribution, %]. That spelling is explained once, under Names on the Dimensional expressions page, and it is the same rule for series names and element names alike.

Filtering a reference

A square bracket after a name reads that series somewhere other than here.

Revenue[Italy]              Italy's revenue, whatever coordinate this formula is at
Revenue[Month[-1]]          last month's revenue
Revenue[~]                  the total, across the whole dimension

What goes inside the bracket is a dimensional expression, and everything that page describes is available here — names, paths, positions, offsets, ranges, set operations.

Several dimensions at once

Separate them with commas. Each item filters one dimension.

Revenue[Italy, 2025]              Italy in 2025
Revenue[Europe/_, Month[-1]]      Europe's countries, last month

Order does not matter, because each item names the dimension it applies to by naming something in it. What you cannot do is put two dimensions inside one item: Revenue[2025 | Italy] is an error, because a union of a year and a country is not a set of anything. Split it into two items.

Consecutive brackets mean the same as one bracket with commas, so Revenue[Italy][2025] and Revenue[Italy, 2025] are the same expression. Use whichever reads better.

Everything except the ones you named

A bare * or _ as a filter item stands for every dimension this reference has that I have not already filtered.

Revenue[_]                  every leaf, in every dimension
Revenue[Italy, _]           Italy, and every leaf of every other dimension

* takes each remaining dimension's top elements; _ takes its leaves. The two are opposites, so a single bracket cannot hold both.

Filtering an expression

A bracket can follow a parenthesised expression or a function call, not only a name.

(Revenue - Cost)[2026]      2026's revenue less 2026's cost
SUM(Revenue)[Europe]        the sum, taken in Europe

The filter distributes over everything inside, so (Revenue - Cost)[2026] is exactly Revenue[2026] - Cost[2026]. Write whichever is clearer; on a long expression the first form usually is.

A bracket binds tighter than any operator, so (A - B)[2026] * C filters the subtraction and leaves C alone. If you want the filter over the whole thing, bracket the whole thing.

Arithmetic and comparison

The usual operators, in the usual order:

^                    raise to a power
* /                  multiply, divide
+ -                  add, subtract
= <> < > <= >=       compare
NOT                  negate a condition
AND                  both
OR                   either

^ groups from the right, everything else from the left, and parentheses override all of it. Comparisons do not chain: write A > 1 AND A < 10 rather than 1 < A < 10.

Reducing many values to one

A filter that selects several elements gives several values, and arithmetic has nothing to work on until you say how to reduce them.

SUM(Revenue[Month[$1:$3]])        the first quarter's revenue
AVG(Cost[Europe/_])               the average across Europe's countries
MAX(Revenue[Product])             the best-selling product's revenue
COUNT(Revenue[Product])           how many products have a value

This is how every sum over a dimension is written. There is no separate family of dimensional reduction functions: you select what you want with a filter, and wrap it in the function that reduces it.

The full list of what is available, with arguments, is on the Supported functions page.

Conditions inside a filter

Two keywords narrow a filter by a condition rather than by name. They differ in what they iterate, and that difference decides which one you want.

WHERE — drop elements from one dimension

WHERE attaches to one filter item and tests each candidate element of that dimension. Elements failing the test are removed from the result.

SUM(Revenue[Product WHERE Margin > 0.1])

Products whose margin is at or below 10% are not in the sum at all. The result is narrower, not masked.

INCLUDE — mask cells across the whole result

INCLUDE sits last in the filter list, after a comma, and tests each cell of the result the filters produced. Cells failing the test become null; the shape is unchanged.

SUM(Revenue[Product, Month[$1:$3], INCLUDE Margin > 0.1])

Every product-month combination is still there — the ones that fail are simply null, and SUM skips them. Because COUNT counts what is not null, pairing the two is how you ask how much, and in how many cases.

A masked cell is a hole in the answer this expression produced, not a gap in the model — the model itself has a value at every coordinate.

On a single dimension the two forms give the same number, and WHERE is the one to write.

Missing values

Null arises from operations, and there are two that produce it:

• A reference past either end of a layer. In the first month, Revenue[Month[-1]] has nothing to read, and in the last, neither does Revenue[Month[1]]. A sequential dimension may declare a boundary element before its first period or after its last; where one exists, it is an ordinary element of that layer, and a reference that steps onto it reads its value rather than nothing.
• Division by zero.

Neither is an error, and neither stops the model. Both give null, and null spreads: anything arithmetic does with a null is null. Nz supplies a fallback; ISEMPTY — the function keeps the older spelling — is how you test for one.

Nz(Revenue / Revenue[Month[-1]] - 1, 0)     growth, or zero where no earlier month exists
ISEMPTY(Revenue[Month[-1]])                 1 when there is nothing to read

A range that runs past the end of a dimension is a different thing and is not null — it is simply shorter. Ask for twelve months in a model holding four and you get four, and SUM adds up the four.

Time patterns

Axmo has no year-to-date function, no same-period-last-year function, and no rolling-total function. They are all written from the notation above, which means they work on whatever your Time dimension actually looks like rather than on the calendar someone assumed.

SUM(Revenue[Year[0]/Month[$1:$Month@Year]])              year to date
SUM(Revenue[Quarter[0]/Month[$1:$Month@Quarter]])        quarter to date
Revenue[Year[-1]/Month[$Month@Year]]                     the same month, last year
SUM(Revenue[Month[-11:0]])                               a rolling twelve months
Revenue / Revenue[Year[-1]/Month[$Month@Year]] - 1       year on year

Note the $ in front of Month@Year, because it is the difference between right and wrong. Month@Year produces a number — 3, in March — and inside a position slot a bare number is an offset. So Month[Month@Year] means three periods forward, and returns a number that looks perfectly reasonable. Month[$Month@Year] means the third month of this scope, which is what these patterns want. The rule is the one on the Dimensional expressions page and it holds everywhere: $ for a position, no $ for an offset.

When a series refers to itself

A closing balance is the opening balance plus the movements, and the opening balance is last period's closing balance. Written directly, that is a series whose formula names itself:

Balance = Balance[Month[-1]] + Net Cash Flow

Axmo supports this, and it is the reason the language exists in the form it does. It also comes with rules about which self-references can be resolved and which cannot, and with a decision to make about where the very first balance comes from.

Those are on the Recurrence rules page.