When a formula refers to other Blocks to iterate over all the items in a Dimension, care is needed to avoid circular dependencies, which risk creating an infinite loop. For more information on circular dependencies, see Troubleshoot circular dependencies.

This page looks at the second of Pigment’s tools for creating iterative calculations, the PREVIOUSOF function, which replaces the PREVIOUSBASE function. It explains how to optimize its use in the context of multiple Block iterative calculations. The first tool, the PREVIOUS function, is for single Block calculations. See Iterative calculations within a Block using PREVIOUS for more information.

Multiple-Block Iterative Calculation

A multiple-block iterative calculation configuration is made up of two things:

• an iteration Dimension

• a list of allowed Metrics

When you create an iterative calculation configuration, all allowed Metrics can be referenced on the condition that they are referenced within the PREVIOUSOF function.

PREVIOUSOF returns the referenced Metric value, which is shifted by one period along the iteration Dimension. Using the PREVIOUSOF function is equivalent to writing the formula 'Metric'dSELECT: 'Iteration Dimension' - 1] but it doesn’t trigger the circular reference error.

In the list of allowed Metrics, you must select all Metrics which are part of the dependency cycle.

Let’s say you want to build this cycle:

In this example, the Metric you’d like to use PREVIOUSOF with is:

• End Inventory

This is because the model is calculating the Beginning Inventory Metric based on the previous time period of the End Inventory Metric.

The list of allowed Metrics should thus contain:

• Beginning Inventory

• Incoming Re-Order

• End Inventory

You can also use PREVIOUSOF as if it were PREVIOUS within a function, by referencing the Metric itself. For example, PREVIOUSOF(‘Ending Inventory’) if used within the Ending Inventory Metric itself, works as if you had used PREVIOUS within the Metric.

Limitations

• The maximum number of allowed Metrics is 10.

• All Metrics in the iterative calculation configuration need to have the iteration Dimension in their structures.

Create an Iterative Calculation

This example explains how to build out an inventory plan in your Application using iterative calculation. For this example, we use the inventory use case described above.

1. In Application Settings, click Calculations, and then click Add an iterative calculation.

2. In the Iterative calculation pane, fill in the following:
   1. Cycle Name: Inventory calculation
   2. Iteration Dimension. Month Dimension
   3. Metrics that can reference each other. All Metrics needed in the iterative cycle.

3. Select Save.

4. In the following Metrics, enter these formulas:
   • Beginning Inventory: PREVIOUSOF('Ending Inventory')
   • End Inventory: 'Beginning Inventory'+'Incoming Re-order'-'Outgoing Sales'

5. Make the Incoming Reorder Metric dependent on the previous month’s End Inventory Metric. In the Incoming Reorder Metric, input the following formula:
   200iADD:Month] - PREVIOUSOF(‘Incoming Reorder’)

You’ve created a model where the Incoming Re-order and Beginning Inventory Metrics rely on the End Inventory Metric for their calculations.

How does it work?

When you create an iterative calculation configuration, the Pigment engine identifies that the Metrics have cyclical dependencies. Pigment calculates the whole cycle iteratively. For each Item of the iterating Dimension, the formulas of each Metric in the cycle are executed sequentially (with some parallelizations under the hood). The order in which each Metric in a cycle is calculated depends on the dependencies between their formulas.

Optimization

Because the Metrics specified as part of a cycle are calculated differently, it's best for performance to omit any unnecessary Metrics from the list of allowed Metrics.

In the example below, the Outgoing Sales Metric is included in the list of allowed Metrics—but isn’t actually used. Any unused Metrics can be removed from the list of allowed Metrics.

The Active icon indicates whether the formulas of the different Blocks generate a cycle or not. If the iterative calculation configuration has an Inactive icon, it means it can be safely removed.

Debugging Formula Errors

When using iterative calculation configuration, the Pigment engine builds a formula that would be subject to the same limitations as the standard PREVIOUS function. As a result, it’s possible to write three valid formulas in three different Metrics but generate an invalid base formula.

For example:

• End Inventory Metric: 'Beginning inventory' + 'Incoming re-order'

• Beginning Inventory Metric: PREVIOUSOF('Ending Inventory')

• Incoming Re-order Metric: 'Beginning inventory' REMOVE: Month]

When this happens, the Metric formula displays an error:

“Error: This Dimension modifier using the Iterating Dimension Month can't be applied to a Metric in the iterative calculation”.

This is not allowed in iterative calculations. However, as with the PREVIOUS function, all modifiers (BY, SELECT, REMOVE, FILTER) are supported as long as they are not referencing the iterating Dimension. All window functions (CUMULATE, MOVINGAVERAGE, MOVINGSUM) are supported within Metrics used in an iterative calculation.

As is the case with PREVIOUS, we do not allow PREVIOUSOF to be used if the iterating Dimension has more than 10,000 Items. Using it with a Dimension larger than this results in a computation error.

Learn more

• PREVIOUS function
• PREVIOUSOF function
• IFBLANK function