IBM Cognos TM1

IBM Planning Analytics Tips & Tricks: The CellValueN Formula

May 19, 2020 by Lee Lazarow Leave a Comment

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.

IBM Planning Analytics Tips & Tricks: CellValueN Formula

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!

IBM Planning Analytics Tips & Tricks: Max Number of ELSEIF

May 12, 2020 by Lee Lazarow Leave a Comment

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!

Tech Bulletin: New IBM Planning Analytics Features for 2020

May 11, 2020 by Lee Lazarow Leave a Comment

IBM introduces new features for Planning Analytics in releases throughout the year. We publish IBM Planning Analytics Tips & Tricks on these features every Tuesday in our Knowledge Center. If you would like to receive these in your inbox every Tuesday, you can subscribe here.

Here we list out which useful features have been released so far this year for PAx and PAW.

New PAx Features in 2020

2.0.50 (Feb)
Design Mode can be used to enable or disable the refreshing of an Exploration View upon every change. As of 2.0.50, you can now also use Design Mode to preserve or clear DBRW formulas when you are copying and pasting values into cells.

2.0.51 (Mar)
As of version 2.0.51, you can now bulk update the data source or package for multiple Exploration Views at the same time.

2.0.52 (Apr)
Various improvements were made to the set editor to make it easier to work with large dimensions. These include the ability to focus on one area at a time, have easy access to various functions, perform drag and drop functionality, and define default settings for the available members and current set panes.

New PAW Features in 2020

2.0.48 (Jan)
Ability to abbreviate numbers to thousands or millions within the Set Format options

2.0.49 (Feb)

Changed hierarchy sorting to an alphabetical approach
Ability to enable security access to processes

2.0.50 – 2.0.52 (Apr)

Set editor improvements (see Pax 2.0.52 for details)
The AgentActions.log file can be used to review the user and time associated with these database actions: start, stop, restart, and end process
Modified various icons and the settings within the refresh menu

If you have any questions about these features, please feel free to reach out to me at llazarow@revelwood.com.

IBM Planning Analytics Tips & Tricks: New Excel Feature – Map Charts

May 5, 2020 by Revelwood Leave a Comment

One of the new features in Excel is the ability to create Map Charts to compare values and show categories across geographical regions.

If you have your own set of data to use skip to step 3. Otherwise, we can accomplish this in a few easy steps using one of Excel’s preset geographical data types:

Once you have your list of geographical values, navigate to the Data Tab > Data Types > Geography.
An option to insert data will appear. This will offer several preset options that will be automatically populated once selected.
Now that data is populated the Map Chart can be created. Highlight your table, navigate to the Insert Tab > Charts > Maps > Filled Map. A Map Chart will now be created

This is a nifty feature in Excel that will help visualize your data. Remember, this feature is already built-in to Planning Analytics Workspace! Learn more about this in our earlier blog post on displaying data in maps.

IBM Planning Analytics Tips & Tricks: Sparklines

April 28, 2020 by Revelwood Leave a Comment

Are you familiar with Sparklines, a rarely used feature in Excel?

What happens when you’d like to provide a visual representation of data but don’t necessarily want, or have the space, for a big chart? Sparklines may be a viable solution.

To add a Sparkline for data, follow these steps:

Once you have your data, navigate to the Insert Tab > Sparklines. In this scenario a Line Sparkline will be used but there are also options for Column and Win-Loss Sparklines.
Once selected, a dialogue box will show two fields:
1. A. Data Range – Data that will appear in the Sparkline
2. B. Location Range – Where the Sparkline will be placed
After filling out the necessary fields, a Sparkline will be created, and the Sparkline Tab will appear. This provides several options to change the appearance of a Sparkline.

Implementing Sparklines is a very easy and effective way to visualize data without the need for charts.

IBM Planning Analytics Tips & Tricks: Bulk Load Template for Cubes with Indices

April 21, 2020 by Revelwood Leave a Comment

Do you have cubes in your IBM Planning Analytics (PA) model that allow for planning with line item detail? The struggle you may come across in creating a template in Excel that automatically loads to the next available line is that such a template will either not allow you to load in bulk or requires you to load to only one center or account combination at a time. Using PA’s Quick Report, you can create an input template that solves both obstacles.

In implementing a Quick Report load template solution, you must include a Count measure in your cube to track which indices are currently used.

Create your Quick Report so that the Index dimension and other dimensions that constitute a unique index row are in the rows and the other dimensions are in the columns. In the example below, every new item entered for a center will require its own index row. The Measure, Version, and Time dimensions are in the columns.

IBM Planning Analytics Tips: How to Bulk Load Cubes with Indices

In the first row, hard-code a dummy line that will not be included in reporting or will not receive any inputs. The reason for this is that a Quick Report always needs at least one data row to maintain the connection to the cube. You can format this row and change the row height to have it appear as a thick border to your users.

IBM Planning Analytics Tricks: Bulk Load Templates for Cubes with Indices

Lastly, for every row that you’d like to be a part of the load area, populate the Index column with a formula that will determine the next row number by pulling the Count measure of “Total Index” for the center, adding the number of times the center appears in the rows above the current row, and add one to increment to the next index.

You can hide the Index column as this column will no longer be controlled by the user.

IBM Planning Analytics Tips & Tricks Video: Formatting Views in Planning Analytics Workspace

April 14, 2020 by Lisa Minneci Leave a Comment

In today’s IBM Planning Analytics Tips & Tricks video, Lee Lazarow, our PF&A Technology Director, demonstrates how to format views in Planning Analytics Workspace (PAW). By using formatting, you will make your views easier to read so your users can spend more time focusing on their data.

Watch the video to learn how to manipulate your views by:

Making your columns wider
Changing rowing heights
Changing fonts and text styles
Adding shading, and more.

You’ll also learn how to start over if you want to change your formatting. Taking the time to format your views makes it much easier for your users to home in on the data and the meaning behind that data.

Want to get our Planning Analytics Tips & Tricks delivered to your inbox every Tuesday? Sign up to get our weekly email of just the week’s tip! And don’t forget to subscribe to our YouTube channel for more Planning Analytics videos.

Watch more Planning Analytics Tips & Tricks videos:

IBM Planning Analytics Tips & Tricks Video: Creating Multi-Level Dimensions

IBM Planning Analytics Tips & Tricks Video: The Hold Feature

IBM Planning Analytics Tips & Tricks Video: Using Words Instead of Numbers

Webinar: The Value of Long-Term Planning

April 9, 2020 by Lisa Minneci Leave a Comment

The value of long-term planning can’t be underestimated. When you have a long-term plan, you have a better understanding of your income and cash flow. This enables you to plan better for resource needs such as personnel, equipment purchases, expansion opportunities and more. Improving your planning process will help you to define your future business strategies and provide a path for achieving your goals.

Join us on Tuesday, April 21, 2020 at 4pm EDT for a live webinar on long-term planning with IBM Planning Analytics. Attendees will learn:

Different approaches for short-term versus long-term planning
The reasons why you should do long-term planning
Factors to consider when defining the long-term planning process

Attendees will also see a live demo of IBM Planning Analytics, which includes two different approaches to long-term planning: driver-based and growth percent planning.

Now is a great time to think about how you approach planning and to learn the benefits of long-term planning. Join us on Tuesday, April 21^st.

IBM Planning Analytics Tips & Tricks: Create New Books with the Diamond Icon

April 7, 2020 by Lee Lazarow Leave a Comment

IBM Planning Analytics pages are defined as “books.” Each book can have multiple components on it (a view, an image, etc.) and the book can contain multiple sheets.

Most users create books by clicking on the “New” icon from the Welcome screen and selecting either a book from a list of templates or via a default format (freeform layout and tabbed workspace).

IBM Planning Analytics Tips & Tricks: Creating New Books

But did you know that you can save time by clicking on a single button on the Welcome screen? The top left corner contains an icon that looks like a diamond.

Creating new books with the diamond icon in IBM Planning Analytics

You will notice that this is an icon (and not just an image) since the image will change slightly as you hover over it. By clicking on this image, you can create a book that uses the default format.

This “hidden” approach in Planning Analytics will help you save time by creating a new PAW Book via a single click.

IBM Planning Analytics Tips & Tricks: The CellValueN Formula

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Max Number of ELSEIF

Read more IBM Planning Analytics Tips & Tricks:

Tech Bulletin: New IBM Planning Analytics Features for 2020

IBM Planning Analytics Tips & Tricks: New Excel Feature – Map Charts

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Sparklines

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Bulk Load Template for Cubes with Indices

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks Video: Formatting Views in Planning Analytics Workspace

Watch more Planning Analytics Tips & Tricks videos:

Webinar: The Value of Long-Term Planning

IBM Planning Analytics Tips & Tricks: Create New Books with the Diamond Icon

Read more IBM Planning Analytics Tips & Tricks:

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

IBM Cognos TM1

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Watch more Planning Analytics Tips & Tricks videos:

Read more IBM Planning Analytics Tips & Tricks:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT