Iterate Formula Details

Detailed information and examples for the Iterate formula.

The Iterate formula is designed to work with embedded formulas for rolling calculations, such as cumulative sum and compound growth.

Using the Prior function, you can also retrieve the value that was calculated for the previous member. The base member in the second argument is always the starting value of the iteration.

Note

Iterate results are reflective of the hierarchy order of the dimension and do not reflect the sorting appearance on the front end. For flat dimensions, where no hierarchy exists, results are reflective of the dimension ID ordering within the model. For more information, see SAP Note 3293548 Information published on SAP site

Syntax

ITERATE([<formula>], [<base member>], [<dimensions>])

Examples

ITERATE(PRIOR () + [Balance], [Balance], [d/Date].[p/CALQUARTER])

In this example, the function calculates a rolling sum along the date dimension split in quarters based on the Balance measure. The second value is the starting value, and corresponds to the value of the base measure. For each hierarchy level, every next value is calculated by adding the previous value to the current value.

The rolling sum is aggregated at each hierarchy level. Here, year, quarters, and months.

ITERATE(PRIOR () + ([Growth]-[Expenses]), [Balance], [d/Date])

In this example, the second member Balance is the starting value, and the next value is calculated using the ([Growth]-[Expenses]) formula. The result of that formula is then added to the results of Balance.

The iteration moves along the Date dimension.

ITERATE(PRIOR () * (1 + [Growth]), [Balance], [d/Date] AND [d/Product])

In this example, the first value corresponds to the Balance value. Every next value is the previous value, increased by a growth factor (usually expressed in percentage). The iteration moves along the date dimension, and for each date, along the product dimension.

ITERATE(PRIOR () * (1 + [Growth]), [Balance], [d/Date])

This example is the same as above, except that the iteration only moves along the Date dimension. The product dimension isn’t taken into account.

Using Inverse Formulas with Iterate

The Iterate formula can be used in conjunction with inverse formulas. Use inverse formulas to specify the first argument of the Iterate formula and specify how exactly to inverse the Iterate formula.

Growing Sum

Let’s use the growing sum example as a basis, with the following formula that calculates a running sum over X along a date dimension:

ITERATE(PRIOR() + [X], [X], [d/Date])

In this example, a possible inverse formula could be:

INVERSE([X] := [SUM] - PRIOR())

Where SUM is the measure defined by the formula. Prior is also supported in inverse formulas. In this case, it evaluates the SUM value of the previous date member.

You can go further and assign a value to PRIOR():

INVERSE(PRIOR() := [SUM] - [X])

This writes the SUM value to the previous date member, which iteratively invokes inverse formulas again. If there’s no other inverse formula, this iterates until the first date member is reached. Note that a data entry on the first member dismisses the inverse formulas and always writes to the base measure.

Balance Sheet

A balance sheet typically consists of three accounts or measures:

Opening, which denotes the value at the start of the month or any other time period.
Transactions, which denotes the changes within the months or other time period.
Closing, which denotes the value at the end of the month other time period.

Iterative formulas allow you to model this in multiple ways. First way would be to make Closing a growing sum over Transactions, and Opening the difference between Closing and Transactions:

ITERATE(PRIOR() + [Transactions], [Transactions], [d/Date])

[Closing] - [Transactions]

Opening has the inverse formula INVERSE([Closing] := [Opening] + [Transactions]) and writes to Closing, which is a calculated account or measure. Transactions in a non-calculated account or measure.

Closing has two inverse formulas: INVERSE([Transactions] := [Closing] - PRIOR()) or INVERSE(PRIOR() := [Closing] - [Transactions]).

By doing this, the value of Transactions is adjusted accordingly when entering data. However, when entering data on Opening, the formula will try to adjust the value of Closing, which cannot adjust the value of Transactions since it’s a dependency of Opening. This would create a circular dependency. As a result, the second inverse formula is used and writes to the previous value of Closing. This will evaluate all inverse formulas again, and therefore write to the previous value of Transactions.