IBM Cognos TM1

IBM Planning Analytics Tips & Tricks: PAW Border Lines

May 21, 2024 by Lee Lazarow

The great thing about explorations is that data appears in a simple grid-like format. This allows you to easily look at your data in an easy-to-read format that is similar to an Excel spreadsheet.

In the example below, it’s easy for your eyes to move up and down to see the expenses and then move left and right to see the departments. And since each data point is within its own box, it’s easy for your eyes to know when to stop.

A screenshot of a table

Description automatically generated

However, the use of boxes for the data also puts boxes in the headers. For some people, the lack of symmetry does not matter. But for those who do not like this look, you can turn off all border lines via a single click within the “Custom” properties of an exploration:

A screenshot of a computer

Description automatically generated

Disabling the border will convert the example above into a display with no lines in either the data or the headers.

A screenshot of a graph

Description automatically generated

Once completed, you can also set the formatting to shade every other line using an approach called “Zebra Strips”.

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: Identifying a Specific Dimension

May 7, 2024 by Lee Lazarow

Have you ever wanted to change (or delete) a dimension, but first wanted to know how this action will impact your model? IBM Planning Analytics Workspace (PAW) allows you to quickly identify all the cubes in a database that include a specific dimension.

Since this is a development task, this approach is defined within a modeling workbench. You can access this option by right-clicking anywhere you see a dimension name within the databases tree (cubes, dimensions, control cubes, and control dimensions) and selecting the option to “View related cubes.”

The resulting list will define the full dimension name (e.g., not the caption) and show all the cubes that include the selected dimension.

If you want to better understand the data in the cube, you can then review any of them by clicking on the cube name and then selecting the option to Open the cubes. An exploration view for each cube will open on separate tabs in the modeling workbench.

This approach will give you a fast way to determine the overall impact of a dimension change.

IBM Planning Analytics Tips & Tricks: Asymmetrical Expand in PAW

April 30, 2024 by Marc Assenza

Have you ever been in a situation where you have stacked dimensions in your view and only want to expand one section of it? In legacy Perspectives, an expansion of one area equates to an expansion of all areas. IBM Planning Analytics Workspace (PAW), version 87, introduced functionality that allows you to expand just one piece at a time.

Here is an example of a simple view that shows Actual Finance expenses by Company.

Table

Description automatically generated

By default, an expansion of the Finance department would show an expansion for all three companies.

A new icon has been added that will allow you to determine how you want to expand and collapse the elements within your view.

When clicked, you will have two options: Symmetrical and Asymmetrical.

Text

Description automatically generated with medium confidence

If we change the approach to Asymmetrical and then expand the departments within company 2, only a subset of the full view is expanded.

This approach will allow you to tighten your analysis and let your users focus on a subsection of your view while still easily having the ability to see the big picture.

IBM Planning Analytics Tips & Tricks: PAW Greenbar

April 16, 2024 by Lee Lazarow

Have you ever generated an IBM Planning Analytics Workspace (PAW) exploration and struggled to read the values in the grid? For example, how easy is it to define the travel cost for department 55 in the screenshot below?

A few years ago, I wrote a blog post about solving this in Excel by using formulas to shade every other row. This approach is built into PAW via an option called “Zebra strips.” You can enable this option within the Custom properties of an exploration:

Once enabled, PAW will lightly shade every other row to allow you to quickly navigate through your exploration. Using the example from earlier, it is now much easier to define the travel cost for department 55.

A table with numbers and a number of sales

Description automatically generated with medium confidence

IBM Planning Analytics Tips and Tricks: Excel’s ARRAYTOTEXT Function

April 2, 2024 by Ivan Cepero

Have you ever needed to export Excel data in a format that can be read by other systems? Although csv files are widely accepted, many legacy systems expect data in an array format. This can be done using the ARRAYTOTEXT formula in Excel.

The syntax of the ARRAYTOTEXT formula is:

=ARRAYTOTEXT(array, format)

Array: the text to convert to an array
Format: the format to apply on conversion
- The only valid values for this parameter are 0 and 1

A standard array format uses a set structure:

The array enclosed by curly braces.
All items in the same row are separated by commas
The end of a row is indicated by a semicolon

For example, this data set …

… would appear like this in array notation:

{“Row1Col1″,”Row1Col2″;”Row2Col1″,”Row2Col2”}

However, the function does not force you to use the standard array format. Using the following table as an example:

Format = 0 (also called the concise format) will join all the data into a single comma delimited list, similar to the TEXTJOIN formula.

Format 1 (also called strict format) will convert the data into an array format. Every item on the same row is separated by a comma and the end of a row is indicated by a semicolon.

As an added feature, the ARRAYTOTEXT formula detects data types and does not convert the numbers to text (numbers are not enclosed by quotation marks).

IBM Planning Analytics Tips & Tricks: PAW “Spacers”

March 19, 2024 by Lee Lazarow

Have you ever created an exploration in IBM Planning Analytics Workspace (PAW) that contains a lot of data and wish that you could separate the information? PAW allows you to insert blank rows and columns called “spacers” into your exploration.

Here is an example of a grid that shows expenses by company:

This subset of data is not very large, but it’s somewhat difficult to differentiate the totals from the details within the grid. This is an example where spacers can help.

You can insert a space by simply right-clicking on the area where you want to insert the blank row or column and then selecting the option to “Add spacers.” You can insert the spacer before or after the area that was clicked.

Here is the same example after inserting a blank row that separates the total expense value from the account values:

A screenshot of a computer screen

Description automatically generated

From there, we can adjust the size of the spacer and update the formatting of the top row to make the total values stand out.

This approach will help make it easier for you to create easy-to-read explorations for your users.

IBM Planning Analytics Tips & Tricks: Excel’s ROUND Function

March 5, 2024 by Lee Lazarow

Most standard financial reports round to some form of a large value. For example, the report may show numbers in millions or rounded to the nearest 100,000.

This is done in Excel via the ROUND function. The function has two parameters:

=ROUND(number, num_digits)

The number defines the initial value to be rounded
The number of digits determines how many digits to round the number

Here is an example of a large value with various rounding results:

But did you know that you have a variety of options for the number of digits?

A positive value rounds to the nearest applicable decimal
A zero value rounds to the nearest integer
A negative value rounds to the left of the decimal point

Here is the same example using negative rounding:

Graphical user interface, table, Excel

Description automatically generated

This approach could also be used to round to the nearest thousand (-3) or the nearest million (-6).

In addition, Excel offers two other options: ROUNDUP and ROUNDDOWN.

ROUNDUP will always round away from zero (e.g., round up)
ROUNDDOWN will always round toward from zero (e.g., round down)

By using a combination of the appropriate ROUND function and a positive/negative number, you can ensure that all your reports show the values you want users to see.

IBM Planning Analytics Tips & Tricks: Excel Switch Function

February 20, 2024 by Ivan Cepero

The SWITCH function in Excel evaluates a condition and then provides a result based on that condition. This function is similar to a SQL CASE statement and allows for up to 126 potential results.

The syntax of the SWITCH function is:

=SWITCH(expression, value1, result1, value2, result2, …, no match value)

The Expression is the value to be analyzed. This can be any formula, including another Excel formula.
- The resulting value can be a text string or a decimal.
Result1 is the value to be referenced/matched. This is the result of the expression.
Value1 is the replacement (switch) value.
The “no match value” is the result if no matches are found.

Here is an example of the formula:

Table, Excel

Description automatically generated

This example returns the month short name for January and February but will return ‘Qtr1-End’ for March. If no match is found, the text value “No Match” will be returned.

This approach will allow you to merge IF statements and LOOKUP statements into a single command.

IBM Planning Analytics Tips & Tricks: PAW Generated Statements

February 6, 2024 by Lee Lazarow

One of the great things about IBM Planning Analytics Perspective’s process editor is that the wizard can be used to create code within your scripts. The results of the wizard based statements are inserted within areas called “Generated Statements.”

However, there may be times when you want to change the code within the generated areas. Maybe you want to temporarily disable some lines from running or maybe you want to add a debugging line within the existing section. Perspectives does not allow you to perform this type of task, but Planning Analytics Workspace (PAW) does!

A user preference is built into PAW’s modeling workbench that allows you to either lock or unlock generated statements. You can access this setting by clicking on the preferences icon on a workbench.

From there, you can toggle the Lock Generated Statements option within the Process editor area.

When unlocking, you will need to refresh the script before the system will allow you to make changes within the previously locked area. The following window will appear as a warning:

This approach will allow you to quickly modify your generated statements while also providing the ability to easily reenable them for future development.

IBM Planning Analytics Tips & Tricks: PAW Border Lines

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Identifying a Specific Dimension

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Asymmetrical Expand in PAW

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: PAW Greenbar

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips and Tricks: Excel’s ARRAYTOTEXT Function

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: PAW “Spacers”

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel’s ROUND Function

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel Switch Function

Read more about visualizations in IBM Planning Analytics:

IBM Planning Analytics Tips & Tricks: PAW Generated Statements

Read more about visualizations in IBM Planning Analytics:

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 about visualizations in IBM Planning Analytics:

Read more about visualizations in IBM Planning Analytics:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT