Virtual Consolidations in SUBNM Formulas

Virtual Consolidations allow you to combine multiple TM1 elements using arithmetic operators directly within a SUBNM formula — without needing to create a formal consolidation in TM1.

Creating a Virtual Consolidation

Open the P&L_2 view from the General Ledger cube in the Slice Cube Viewer.
Export it to Excel as a Slice.
Double-click on a SUBNM formula (e.g. the Region dimension in cell B4).
In the Subset Editor, hold CTRL and select multiple elements (e.g. England and Scotland).
Click OK and refresh the sheet (SHIFT+F9).

Understanding the SUBNM Virtual Consolidation Syntax

After selecting multiple elements, the SUBNM formula will look like this:

Symbol	Meaning
`#`	Indicates a Virtual Consolidation
`3`	Element index for England
`+`	Addition operator
`5`	Element index for Scotland

Supported Arithmetic Operators

You can use any standard arithmetic operator inside the virtual consolidation:

Operator	Effect
`+`	Sum of elements
`-`	Difference between elements
`*`	Product of elements
`/`	Division of elements

Example – Variance between England and Scotland:
Change #3+5 to #3-5 in the SUBNM formula, then refresh the sheet.

Using Named Ranges in Virtual Consolidations

You can also use Excel Named Ranges instead of element indices:

Enter the element names (e.g. England and Scotland) in empty cells.
Select both cells and enter a name in the Named Range field, then press ENTER.
In the relevant SUBNM formula, replace the element reference with the named range you created.

The cell will now calculate England plus Scotland dynamically, and the named range can be reused across the workbook.