MODLR

SEQUENCE References ​

When to use: Cumulation calculations, Rolling Balances (e.g. in a Balance Sheet), Averaging multiple months or time elements.

Description: The sequence function alters the provided Direct Reference by a specified number of positions along a specified hierarchy and dimension.

Syntax ​

vb
SEQUENCE("DimName", "HierarchyName", PositionalChangeNumber, ["Direct Reference"])
SEQUENCE("DimName", "HierarchyName", PositionalChangeNumber, ["Direct Reference"])

Simple Example ​

Commonly in a Balance Sheet we want to cumulate the movements of monthly transactions into a rolling balance across accounts. Assuming we have a measures dimension with a Default hierarchy as follows:

  • Closing Balance
    • Opening Balance
    • Movement

Commonly in a Balance Sheet we want to cumulate the movements of monthly transactions into a rolling balance across accounts. Assuming we have a measures dimension with a Default hierarchy as follows:

Restrict to "Opening Balance"

vb
SEQUENCE("Time", "Month List", -1, ["Closing Balance"])
SEQUENCE("Time", "Month List", -1, ["Closing Balance"])

Complex Example ​

Sometimes we may want to calculate the average of the last three months balances to create a rolling average.

Assuming we have a Profit and Loss model and we are populating the Forecast with Actuals up until the current month and then from there we want to take a rolling 3 month average and apply a modification % to that value monthly.

Restrict to: "Amount" from the Measures dimension and "Forecast" from the Scenario dimension

This will take the previous 3 months and then average them and multiply the result by a modifier %.

vb
(
SEQUENCE("Time", "Month List", -1, ["Amount"])+
SEQUENCE("Time", "Month List", -2, ["Amount"])+
SEQUENCE("Time", "Month List", -3, ["Amount"])
) / 3 * (1+["Modifier %"])
(
SEQUENCE("Time", "Month List", -1, ["Amount"])+
SEQUENCE("Time", "Month List", -2, ["Amount"])+
SEQUENCE("Time", "Month List", -3, ["Amount"])
) / 3 * (1+["Modifier %"])

If the "Modifier %" is -10% for the month being calculated it will take the last 3 months average and reduce that by 10%.

Copyright © 2024 MODLR