lee lazarow

Rules in IBM Planning Analytics allow you to reference components of a cube as part of your formula … either from the cube you are currently in or from another cube. This is done via the use of the DB function. The syntax of the DB function is:

DB(cube, elem1, elem2, …)

Where each elem value references an element from the dimensions of the cube, in the order of the cube definition.

This is a great formula if you know all the dimensions of your cube in the proper order. But what if you don’t want to have to define every dimension in your cube?

Planning Analytics also allows you to use a formula called CellValueN. The syntax of the CellValueN function is the same as the other formula:

CELLVALUEN (cube, elem1, elem2, …)

… but there are some differences when using this formula.

• You do not have to reference every dimension! Any undefined dimensions are the equivalent of using the exclamation point, often called a “bang.” This means that you only need to reference dimensions/elements that are different from the source value.
• Since you are not putting your dimensions in order, you must reference the dimension name in front of each element. The dimension name must be written within a set of single quotes, a colon is then used without quotes, and the element is then written within another set of single quotes.

For example, account 605100 would be written as ‘Account’:‘605100’

• There are different versions of the rule. One version is CellValueN which pulls numeric values and one version is CellValueS which pulls string values.

Here is an example of the rule. Note that the cube contains seven dimensions but the formula only references two of them.

This approach allows you to simplify your rules by referencing a data point without having to reference every dimension within the cube.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters! You can also sign up to get our Planning Analytics Tips & Tricks sent directly to your inbox!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula

IBM Planning Analytics Tips & Tricks: IFERROR

IBM Planning Analytics Tips & Tricks: Excel’s CONCAT and TEXTJOIN Functions

Most of you know the concept of an IF statement within TurboIntegrator

IF (expression); statement1; ENDIF;

… and most of you also know the concept of nested-IF statements

IF (expression);     statement1; ELSEIF (expression);     statement2; ENDIF;

… and most of you also know the concept of generically using an ELSE command as a catch-all

IF (expression);     statement1; ELSEIF (expression);     statement2; ELSE;     statement3; ENDIF;

But did you know that Planning Analytics has a maximum number of nests that can be included within a TurboIntegrator process?  You can nest up to 20 ELSEIF/ELSE statements in a process. If you exceed 20 nested statements then you will receive an error when attempting to save the process.

If you come across situations where you are using a lost of nested IF statements, then there may be a way to re-assess the expressions that are being used.  Give us a call!

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters! You can also sign up to get our Planning Analytics Tips & Tricks sent directly to your inbox!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula

IBM Planning Analytics Tips & Tricks: IFERROR

IBM Planning Analytics Tips & Tricks: Excel’s CONCAT and TEXTJOIN Functions