Variable Modifiers in Formulas

Last updated on 2025-03-03

Overview

When you reference a variable in a formula, there are two key ways to modify it - using Dimensions (if applicable), and Time.

This article contains the following sections:

Time Modifiers
Types of Time Modifiers
Dimension Modifiers

Time Modifiers

Time modifiers allow you to reference various different time instances of a variable.

Time Modifier

Types of Time Modifiers

The following types of time modifiers are available:

This 'month': returns the current value of that variable

Previous 'month': returns the previous value of that variable, relative to the time step in which the formula is being applied.

Example: In June, Customers[previous] corresponds to the value of Customers in May.

By default, when you reference another variable, xP&A will return the current value of that variable. When you reference the variable itself, xP&A will return the previous value of that variable.

Look-back and look-forward: returns the value of that variable x days/weeks/months/years in the past or future, from "t" (the current time step).

Example: if you want xP&A to return the value 12 months ago, your time modifier would be t-12. Conversely, if you wanted to return the value three months in the future, your time modifier would be t+3.

Shows a formula in which the time modifier '6 months ago' is used

Looking back 6 month ago

In a look-back or look-forward, you may reference dates that are before the start, or after the end of a model's date range. These cells will evaluate to the empty value of the variable (ie., either 0, or the none value).

Final: returns the value of the final timestep of the model for that variable. Note that if you extend your model time settings, any variables referencing the final time step will auto-update.

Absolute: returns the value of a single, particular timestep for that variable. e.g. 1 refers to the 2nd timestep of the model, 0 refers to the 1st timestep of the model etc. You can also just type in date(YYYY,MM) instead of the timestep index number.

Spans are a sequence of time steps, e.g. past six months in a monthly model would be t-6:t-1.

Spans always need to be aggregated, e.g. wrapped in a sum or avg function.

All: returns the sum of all time steps for a variable.

Shows the time modifier 'All' used in a formula.

Using 'All' in a formula

Span with a custom start/end-point

E.g. first 6 days/weeks/months/years of your model: 0:5 (absolute)

Shows the time modifier 'first 6 months' used in a formula.

Using a span ('first 6 months') in a formula

Past three days/weeks/months/years (relative, useful for rolling averages)

Shows the time modifier 'last 3 months' used in a formula.

Using 'last 3 months' in a formula

Dimension Modifiers

Dimension modifiers allow you to slice and dice referenced variables within formulas, using the related dimensions and dimension items.

You can use the following dimension modifiers to modify referenced variables within formulas:

Once you have linked dimensions together, you can then easily slice variables along different dimensions.

Example: We have an Employee dimension, which is linked to a Team dimension. By default, we've worked out Salaries by Employee, and now we're interested in seeing Salaries by Team.

Click on the Dimension icon in the referenced variable in the formula, and then select the desired dimension and click Group by.

Shows the option 'Group by' from the menu of a linked category.

Using 'Group by'

If you want to "Group by" multiple dimensions at one time just hold down Shift and click each of the dimensions you want to select. e.g. if you want to group by Team and then Role. This displays as multiple layers of nesting.

Sometimes you'll want to select a single item from a dimension and use it in a formula.

Click the Dimension icon in the referenced variable in the formula, select the desired dimension, and then click the individual item you want to filter by.

Example: If you want to do a calculation only based on the Engineering Team's salaries, you would select Engineering under Department.

Shows the option 'Filter by' that can be used in a categorymenu to filter category by one of its category items

Filtering by category item

If you want to filter for multiple dimension items, just hold down Shift and click each of the dimension items you want to select.

Sometimes you might want to aggregate away a dimension, if you no longer care to see that level of granularity.

Example: If you have filtered Salary by Employee for just the Engineering team, but no longer care to see the Employee breakdown, you can aggregate the Employee dimension.

Use the two commands Aggregate (Sums across dimension) and All (Keep dimension intact) to remove or re-add the breakdown of a dimension. To do this, click on the variable in the top bar:

Shows the options 'Aggregate (Sums across category)' and 'All (Keep category intact)' in the menu. of a category

Aggregating away a category

There are a few other ways that you can use or manipulate dimensions and dimension items within variables and formulas, that are not variable modifiers in formulas.

Aggregating at the Variable Level

Sometimes you might want to change how a variable aggregates its dimension items. xP&A's default is to sum the dimension items, but see Dimension aggregation for more options.