New TM1SET function supports alternate hierarchies

TM1SET, a new worksheet function that supports alternate hierarchies, is now available in IBM® Planning Analytics for Microsoft Excel and IBM Planning Analytics TM1® Web. Until now, worksheet functions in both Planning Analytics for Excel and TM1 Web only supported default hierarchies.

Important: To use the TM1SET function, you must be on at least Planning Analytics for Microsoft Excel 2.0.81, TM1 Web 2.0.81, and IBM Planning Analytics Workspace 2.0.80.

Syntax

A dynamic and versatile function, the TM1SET takes nine arguments, and you can configure the function’s output.

=@TM1SET(Host, Server, Dimension, Hierarchy, Set Expression, Selected Member, Session Set Id, Active Alias, Output type)

Features

Much like the SUBNM formulas in TM1 Web, cells that contain the TM1SET function display a drop-down.

TM1SET supports set editor integration in Planning Analytics Workspace websheets. You can click a TM1SET cell to open the set editor and change the set definition such as selecting a different hierarchy.

Note: TM1SET in Planning Analytics Workspace on Cloud supports the new experience set editor. If you are using Planning Analytics Workspace Local, TM1SET uses the default set editor based on your settings.

Set editor in Planning Analytics Workspace websheets

In stand-alone TM1 Web, you cannot open the set editor from a TM1SET cell. Instead, click the cell and change your set selections by using TM1 Web’s subset editor (member drop-down selector).

Note: To open the set editor or use the member drop-down in TM1SET cells, the cells must be in a tracked area named range. Tracked area named range implementation takes place in Planning Analytics for Excel. For more information, see TM1SET.

A powerful feature of TM1SET is its true reference traversal capability. TM1SET’s arguments can be written inline, or they can be written in other cells and referenced in the TM1SET function. When you change a TM1SET function (for example, when you select a different hierarchy) that change applies to TM1SET’s output and to any cells that might be referenced in the formula.

Synchronization is also supported in Planning Analytics Workspace websheets. When you enable synchronization in a websheet, any change to the set definition of the TM1SET is synchronized across the Planning Analytics Workspace websheet or book.

Note: Synchronization dimensions are currently not controlled by the tracked area named range. You might see untracked TM1SETs in the synchronization side panel.

For more information, see TM1SET.