IBM Cognos TM1

IBM Planning Analytics Tips & Tricks: The Excel DATE Function

June 9, 2020 by Lee Lazarow Leave a Comment

Many of you already know about Excel’s DATE function. The function is used to create a date by defining specific values for the year, month and day.

DATE(2020,7,20) gives a result of 7/20/2020.

And some of you know that you can also use formulas to calculate a date.

If a value of 5 exists in cell C4 then

DATE(2020,7,20+C4) gives a result of 7/25/2020.

But did you know that the formula will also check for valid dates and, if needed, shift the month automatically?

DATE(2020,7,35) gives a result of 8/4/2020

(since there are not 35 days in July!)

and

DATE(2020,7,30+C4) gives the same result

Not only can this formula be used to calculate variable dates, but it will also ensure that you don’t incorrectly calculate a value that doesn’t exist on the calendar. After all, we wouldn’t want Julius Caesar coming back to question our use of “his” month!

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!

IBM Planning Analytics Tips & Tricks: Using Words and Numbers Together

June 2, 2020 by Lee Lazarow Leave a Comment

In an IBM Planning Analytics Tips & Tricks video, I demonstrated the concept of using words to manipulate your views. This approach also applies to entering data. All of you know that you can enter data by typing a number into a writeable cell.

IBM Planning Analytics Tips & Tricks: Using Words and Numbers

Some of you know that you can use commands such as “2k” to enter a value that will be converted into a number.

IBM Planning Analytics Tips: Using Words and Numbers

And some of you also know that you can type commands such as “grow10” on an existing number to populate the rest of the year.

IBM Planning Analytics Tricks: Using Words and Numbers

But did you know that you can merge all of this together into a single entry? For example, if I type “5kgrow20” in January then 5,000 will be input into January and a 20 percent growth rate will apply to each of the other months.

Using words and numbers in IBM Planning Analytics

The combination of numbers and phrases in a single cell will help you quickly and easily create plans for multiple time periods.

IBM Planning Analytics Tips & Tricks: The Data Load Process

May 26, 2020 by Lee Lazarow Leave a Comment

In an IBM Planning Analytics Tips & Tricks video, I showed how to use a drag and drop approach to load data into a cube. Since that time, the wizard has been updated to allow you to save your import as a new TurboIntegrator process and to also schedule the process within a chore.

After dragging the file onto the cube, the first screen that appears is a confirmation of the file with some file-based setup aspects such as the delimiter, the quote character and the number of header rows:

IBM Planning Analytics Tips & Tricks: The Data Load Process

After clicking the continue button, the second screen appears which allows you to define the columns and aspects associated with the data load (before and during):

The Data Load Process in IBM Planning Analytics

The bottom, right corner of this screen allows you to save the import as a process and/or a chore:

IBM Planning Analytics Tips: The Data Load Process

Once executed, the process will be saved and can be referenced using PAW’s process functionality:

IBM Planning Analytics Tricks: The Data Load Process

This approach within Planning Analytics allows you to use a drag and drop approach to create and save Planning Analytics code.

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 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: The Excel DATE Function

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Using Words and Numbers Together

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: The Data Load Process

Read more IBM Planning Analytics Tips & Tricks:

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:

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:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT