A common modeling need when building a Pigment Application is to have a formula iterate over all the Items in a Dimension, using a calculation from one Item to calculate the next.

This page introduces and explains the PREVIOUS function, one of Pigment's tools for creating iterative calculations. This function is used within a single Block. However for multi-Block calculations, you need to use the PREVIOUSBASE function. See this usage article for more information on using PREVIOUSBASE.

About circular dependencies

The formula below is an example of a circular dependency within a single Block:

Metric A = 'Metric A'tSELECT: Month - 1] + 1

It attempts to increase Metric A by one month-on-month, but would result in an error message, as Metric A cannot reference itself in the formula. Each time a formula is created or edited, the Pigment engine checks for circular dependencies, flagging errors with pop-up messages such as the below:

It does this to prevent an infinite loop of calculations. To make iterative calculations work, Pigment uses two tools, as shown in the table below:

The first of these is explored in this article. For the second, see the article on this link for guidance.

Single Block Iterative Calculation: PREVIOUS

When you need to refer to data within the same Metric, Pigment offers a special function: PREVIOUS. PREVIOUS returns the value of the previous cell of the current Metric in the iteration Dimension.

Using PREVIOUS means that the value of a given cell depends on the calculation of the previous one in that Metric. The Pigment engine therefore needs to calculate the final value of the previous cell in order to replace the PREVIOUS expression in the current cell.

For this reason, the engine computes the whole formula, including any syntax before and after the PREVIOUS expression, before moving on to the next cell. As the scope of iteration is the whole formula and Metric, any post-processing, such as filtering or certain other operations, should happen in another Metric that references the current one. See Special Cases below for an example.

You can also control whether PREVIOUS returns one item prior, or some other number. While PREVIOUS(Month) provides the cell value from the month prior, PREVIOUS(Month,2) provides the value from two months prior. This optional parameter is one by default but can be any positive integer.

Below are examples demonstrating ways to use PREVIOUS.

Example: increment a Metric based on the value of the previous month

This example shows how PREVIOUS provides values for each item along the Month Dimension, increasing the value of the previous month's item by one, starting from a manual input of 10 in the Jan 24 cell. The formula applied is:

PREVIOUS(Month) +1

Example with dynamic offset: increment a Metric based on the value from two months prior

The PREVIOUS function also has a second optional parameter. This parameter specifies the number of Dimension items (not always time periods) to offset. It must be a positive constant integer, or a Metric of type Integer defined on the same Dimensions as the Metric. A negative value is automatically ignored. The formula applied is:

PREVIOUS(Month,2) +1

Example with other Metrics: calculate a cash flow balance

This example combines the PREVIOUS expression with other Metrics to calculate a cash flow balance over multiple periods. The formula is:

Cash = PREVIOUS(Month) + Income - Expense

Example showing circular dependency: plan inventory

In the warehouse inventory example below, the following details are available:

• Beginning Inventory: End inventory from the previous month
• Incoming Re-order: Restock of inventory arriving in the company’s warehouse this month
• Outgoing Sales: Sales orders leaving the warehouse
• End Inventory: Beginning Inventory + Incoming Re-order - Outgoing Sales

You could set up a formula based on the following:

End Inventory = Beginning Inventory + Incoming Re-order - Outgoing Sales

But the Beginning Inventory Metric itself requires the End Inventory Metric, using the SELECT modifier:

Beginning Inventory = End Inventory>SELECT: Month - 1].

This results in a circular reference and error message, as the formula for End Inventory includes Beginning Inventory, whose formula in turn includes End Inventory. To resolve the fact that End Inventory needs to refer to data within itself, the formula has to use PREVIOUS, as only the PREVIOUS function is recursive:

End Inventory = PREVIOUS(Month) + Incoming Re-order - Outgoing Sales

Special cases

Certain operations are currently not compatible with PREVIOUS. See a full list on the PREVIOUS function page. Other operations, while compatible, have important implications.

In the below example using IFBLANK, the first row is a series of Booleans. The second and third rows use IFBLANK to populate the row with patchy data from a Block called ‘Wk sales data’, using PREVIOUS to fill any blank cells with the value from the previous Item. The difference between them is that the third row also contains c'Filter boolean'] at the end. The modeler’s aim is to replicate the second row but only where the Boolean is TRUE, leaving blanks everywhere else.  

However, the Pigment engine has returned blanks everywhere, including in cells marked TRUE. This is because the IFBLANK function is applied in its entirety cell by cell, as shown below:

1. Until Item WC 2023-12-18, the Pigment engine issues blanks in each cell as the Booleans are FALSE.
2. From Item WC 2023-12-25 onward:
   1. The Boolean is TRUE, so the filter is not applied.
   2. IFBLANK’s first argument finds a blank in ‘Wk sales data’.
   3. This triggers IFBLANK’s second argument PREVIOUS(Week), which finds a blank in the previous cell.
   4. BLANK is returned.

To avoid situations such as these, modelers should use the expression containing PREVIOUS in a separate Metric before applying a Boolean filter. In the above example, modelers should create a separate sales data Metric containing the formula from the second row. Then in this Metric they should apply the filter on that sales data Metric: ('Sales data Metric' h'Filter boolean']).

Learn more

• PREVIOUS function
• PREVIOUSBASE function
• IFBLANK function