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.

This page looks at the second of Pigment’s tools for creating iterative calculations, the PREVIOUSBASE function and 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. Find out more about using it on this link.

About circular dependencies

The Pigment modeling engine computes formulas in sequence. Each time a formula in a Block is created or edited, all of the Blocks using the original Block in their formulas are automatically recalculated. Creating a circular dependency could lead to an infinite loop of calculations.

For that reason, each time a formula is created or edited, the Pigment engine checks if the new formula would create a circular dependency, flagging errors such as the below when circular referencing cannot be resolved:

The table below shows the two contexts for circular-referencing formulas, which require different solutions. The first, for single Blocks, is explored in the article on this link. The second is explored below.

Multiple Block Iterative Calculation

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

• a base Metric
• an iteration Dimension
• a list of allowed Metrics

When you create an iterative calculation configuration, all allowed Metrics can reference the base Metric in their formula on the condition that they use the PREVIOUSBASE function.

PREVIOUSBASE returns the base Metric value, which has shifted by one period along the iteration Dimension. Using the PREVIOUSBASE function is equivalent to writing the formula 'Base Metric'uSELECT: '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, your base metric 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 only contain:

• Beginning Inventory
• Incoming Re-Order

Limitation:

• Allowed Metrics cannot use the PREVIOUS function.
• The maximum number of allowed Metrics is 10.
• All Metrics in the iterative calculation configuration, that is, base and allowed Metrics, 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, select the following:
   a. Base Metric. End Inventory Metric
   b. Iteration Dimension. Month Dimension
   c. Allowed Metrics. All Metrics needed in the iterative cycle.
3. Click Save.

   

   
    
4. In the following Metrics, enter the following formula:

• Beginning Inventory: PREVIOUSBASE()
• End Inventory: 'Beginning Inventory'+'Incoming Re-order'-'Outgoing Sales'

Next you need to make the Incoming Reorder Metric dependent on the previous month’s End Inventory Metric.

5. In the Incoming Reorder Metric, input the following formula:
   200uAdd: Month] - PREVIOUSBASE()

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

Further Information

How does it work ?

When you create an iterative calculation configuration, the Pigment engine consolidates the formulas of the Metrics involved into a single base formula to resolve circular dependencies. This base formula, which incorporates the logic of all the formulas in the cycle, is then used to compute the base Metric.

In the Inventory Planning example, the base Metric formula is:

'Beginning Inventory' + 'Incoming Re-order' - 'Outgoing Sales'

Automatically, this formula transforms to include the Beginning Inventory and Incoming Re-order formulas. The base formula is:

(PREVIOUS(Month)) + (200mAdd: Month] - PREVIOUS(Month)) - 'Outgoing Sales'

Optimization

Because the base Metric formula combines all allowed Metrics into one big formula, 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 Metrics with the status Not part of a circular dependency 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 base 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: PREVIOUSBASE()
• Incoming Re-order Metric: 'Beginning inventory'

When this happens, the base Metric formula displays an error, and the error message indicating that the different formulas of the Block cycle are incompatible.

The Initial Error section identifies the formula error encountered by the base formula.

With the above example the merged base formula would be :

PREVIOUS(Month) + PREVIOUS(Month) REMOVE: Month]

This base Metric formula would fail because the REMOVE Dimension modifier is not allowed on the PREVIOUS function result.

Learn more

• PREVIOUS function
• PREVIOUSBASE function
• IFBLANK function