New Upload weather data from web services into TM1/Planning Analytics — Read the post →

Using Excel Ranges within TM1 Functions

Slice allows TM1 elements to be aggregated by referencing Excel named ranges directly in TM1 formulas, enabling flexible, multi-element lookups without modifying the TM1 model.

How It Works

  1. Create a named range in Excel that contains a list of element IDs or element aliases.

  2. Reference the named range inside a DBRW (or DBR) formula in place of the usual SUBNM cell reference for a dimension.

Slice will aggregate the values for all elements in the named range and return the result — effectively summing the elements in the range.

Example

Suppose you have a named range called myRegions containing the values VIC, QLD, and Tasmania.

In your DBRW formula, replace the cell reference pointing to the SUBNM for the Regions dimension with myRegions:

The result will be the sum of VIC, QLD, and Tasmania for the other dimension intersections specified in the formula.

Tip: This technique works alongside Virtual Consolidations and Virtual Expressions as an alternative method for ad hoc cross-element aggregation directly in Excel.