Rolling Values Using Formula

Rolling balances and depreciation calculations can be implemented using the SEQUENCE workview function.

Function	Description
SEQUENCE	The sequence function returns data from a specified relative location in the same cube but changes one element from one dimension based on a relative position number and a hierarchy. Syntax: SEQUENCE("DimName", ”HierarchyName”, PositionalChange, ["Relative Cell"]) Example: SEQUENCE("Time", "Month List", -1, ["Closing Balance"]) This example will return the closing balance of the previous month.
ISLEVELZERO	The isLevelZero function returns 1 if the element from the specified dimension has no child elements. Syntax: ISLEVELZERO("DimName") Example: IF( ISLEVELZERO("Time") = 1 , CONTINUE , 0 )
ELEMENT	The element function returns the name of the element (excluding the hierarchy prefix) for the cell being evaluated. Syntax: ELEMENT("DimName") Example: ELEMENT("Time")
POSITION	The position function returns the index of an element within a specific dimension and hierarchy. Syntax: POSITION("DimName", "HierarchyName", "ElementName") Example: POSITION("Time", "Month List", "2017 - Jan") This example will return 1 if the element “2017 – Jan” is the first element in the “Month List” hierarchy.

TIP

When using the sequence function, it is possible to create a circular reference. MODLR has some detection for circular references however if the circular loop spans enough distinct cube cells it will likely terminate the MODLR Instance. A log will be generated in this instance.

In the above example, the Time and Measures dimensions are being hidden from the workview. For the time dimension the initial six columns are against a No Time element and the subsequent columns reflect 12 months of the current year. For the Measures dimension the first six columns use elements which are reflected in the custom header, the subsequent columns use an element called Depreciation.