Iterative Calculation & Circular Referencing

A common modeling need when building a Pigment Application is to have an iterative calculation. An iterative calculation in Pigment is where a formula iterates over all the items in a Dimension, performing a specified calculation on each item in sequence.

To support iterative calculations, Pigment provides two functions:

• PREVIOUS(). To create an iterative calculation while referencing the same metric.
• PREVIOUSBASE (). To create an iterative calculation while referencing another metric.

About Block-Level Circular Dependencies

The Pigment modeling engine computes Blocks in sequence, meaning one after the other. Each time a Block is updated, all of its dependent Blocks, or more specifically the Blocks using this Block in their formulas, is automatically recalculated. Creating a loop of Block dependencies 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 will create a Block-level circular dependency. If this happens, the formula will be rejected.

Multiple Block Circular Dependency

Let’s say a model has two Metrics: Metric A and Metric B.

Metric A depends on Metric B with the formula: Metric A = 'Metric B' + 1

If a formula is input on Metric B referencing Metric A ( Metric B = 'Metric A' + 1), it will be rejected. This is because, without any special function, this creates a circular dependency, which isn’t allowed.

Single Block Circular Dependency

Similarly, if you input a formula on Metric A referencing Metric A ( Metric A = 'Metric A'[SELECT: Month - 1] + 1), this will be rejected. For the same reason, Pigment prevents Members from self-referencing Blocks in the formula.

When circular dependency errors occurs, Pigment offers two special functions to resolve them by adjusting the modeling, PREVIOUS () and PREVIOUSBASE(). 

Single Block Iterative Calculation 

As shown in Single Block Circular Dependency example, a standard Pigment formula doesn’t allow self-referencing within the same Block.

For example, you can’t use the formula: 'Metric A' = 'Metric A'[SELECT: Month] + 1 However, the PREVIOUS function allows you to do this.

The PREVIOUS function always refers to the Metric in which the formula is written, using a specified positive integer offset. The modeler must specify which Dimension of the Block to offset. The time Dimension is the most common choice, but not the only option.

Examples of how to use the PREVIOUS function

Increment a metric based on the value of the Previous Month

This example shows how to go through each item in the Month Dimension and increase the value of the previous month's item by 1. The data in Jan 24 is entered as manual input.

To do this, use the formula: PREVIOUS(Month) +1

Calculate a Cash Flow Balance

An example of a common financial use case is a cash flow statement. You can use the PREVIOUS function can be used to calculate a cash flow balance.

To do this, use the formula: Cash = PREVIOUS(Month) + Income - Expense

Simple Inventory Planning

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

• Beginning Inventory: Inventory for the end of 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

In Pigment, the Beginning Inventory Metric is calculated from the End Inventory Metric. If you write the following formula, it results in a circular reference:
End Inventory = Beginning Inventory + Incoming - Outgoing

To calculate the Beginning Inventory Metric, use the SELECT modifier:

End Inventory[select: Month - 1].

This brings the value of End Inventory from the previous month to Beginning Inventory.

However, if the formula for End Inventory formula is entered again, it still results in a circular reference:

Beginning Inventory + Incoming Re-order - Outgoing Sales

This is because the Beginning Inventory Metric contains End Inventory in its formula. Pigment detects it as a circular reference and restricts a Metric to reference itself in the formula.

In Pigment, only the PREVIOUS function is recursive, and it allows the calculation to work as needed. The PREVIOUS function returns the value of the same Metric offset by a number, which by default is one.

To make this calculation work in Pigment, and to avoid the circular reference, the formula you need for the End Inventory Metric is:

PREVIOUS(Month)+ Incoming Re-order - Outgoing Sales

Dynamic Offset

The PREVIOUS function also has a second optional parameter. This parameter specifies the number of Dimension items (not always periods) the offset needs to be done.

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.

It must be a positive constant integer or a metric of type integer defined on the same dimensions as the metric. A negative value will be ignored automatically.

Limitations When Using PREVIOUS

To avoid cell-level circular dependencies, Pigment restricts the use of the PREVIOUS function in the formula language syntax.

1. Dimensions modifiers on expressions that use the PREVIOUS function
   The Dimension modifiers ADD, REMOVE, SELECT, and BY cannot be applied to an expression containing a PREVIOUS function. For example, you can’t use this formula:
   PREVIOUS(Month)[BY: Month.Quarter]
   
   The PREVIOUS function cannot be used inside a BY modifier, for example: metric[BY: PREVIOUS(Month)

2. Combining Multiple Dimensions to Offset
   It is not possible to combine multiple PREVIOUS() functions using different Dimensions to offset in a given formula.
   For example, you can’t use this formula: PREVIOUS(Month) + PREVIOUS(Product)
    
3. Advanced Function Compatibility

   The PREVIOUS function is incompatible with some of the other functions available in Pigment.
   These include:

• CUMULATE / DECUMULATE / SPREAD
• ISBLANK/ ISNOTBLANK
• IFDEFINED
• IRR / XIRR/ NPV
• MOVINGSUM / MOVINGAVERAGE
• DOUBLE_EXPONENTIAL_SMOOTHING / SIMPLE_EXPONENTIAL_SMOOTHING
• FORECAST_LINEAR, FORECAST_ETS, SEASONAL_LINEAR_REGRESSION
• DAYSINPERIOD, PRORATA, DAYSINPERIODWITHHOLIDAYS, PRORATAWITHHOLIDAYS
• NETWORKDAYS
• RANK

For example, you can’t use this formula:  CUMULATE(PREVIOUS(Month), Product)

4. Offset
   The offset parameter needs to be positive (negative offsets are ignored and considered blank). For example, PREVIOUS(Month, -1) will be ignored. 
    
5. Performance
   Formulas using the PREVIOUS function don’t benefit from some optimizations in Pigment engine. As a best practice, avoid using the PREVIOUS() function unless necessary.

Multi-Block Iterative Calculation

For some use cases, the PREVIOUS function can’t prevent circular references, and instead you need to use the PREVIOUSBASE function.

Consider the previous Simple Inventory Planning example, and the Incoming Re-order is defined as an expression of End Inventory.

For instance, if the Incoming Re-order is set to fill the stock by 200 units based on last month's end-of-period stock, and the formula in the Incoming Re-order Metric is 200 - 'End Inventory[select: Month - 1], the formula won’t allow it.

The dependency diagram shown below wouldn’t be possible:

Concept

To solve this problem you can use Iterative Calculation. The idea is to declare a list of Metrics on which the standard circular reference detection is disabled.

An Iterative Calculation configuration is made up of 3 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'[SELECT: '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 in Multi-Block Iterative Calculation 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 it 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:
   200[Add: 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)) + (200[Add: 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'[REMOVE: Month]

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 will fail because the REMOVE Dimension modifier is not allowed on the PREVIOUS() function result.