TM1

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, 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: 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:

A screenshot of a computer screen

Description automatically generated

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 Pie Chart Total

January 23, 2024 by Lee Lazarow

Pie charts are a type of visualization known as composition. Composition charts are designed to show the parts of a whole. This is a great way to look at the pieces of total. For example, the branches in a region or the months in a year.

Here is an example of a pie chart that shows operating expenses for a year:

IBM Planning Analytics Tips & Tricks: PAW Pie Chart Total

The great thing about this chart is that you can easily see the numbers for each quarter. I can also quickly see comparative values such as Q1 being much larger than Q4. However, what I cannot see is the total for the year.

IBM Planning Analytics Workspace allows you to include a “donut hole” and include the total in the center of the pie. This is enabled within the chart area of the properties by defining the radius percent.

When combined with the option to “show inner label”, here is an example of the same chart with a donut hole of 50%:

This approach will help you expand your use of comparative pie charts.

IBM Planning Analytics Tips & Tricks: Excel TEXT Function

January 9, 2024 by Ivan Cepero

Have you ever merged text and a calculated number into a cell and saw that the format of the number was hard to read? The TEXT function in Excel lets you convert the number into a more professional format.

The syntax of the TEXT function is:

=TEXT(value,format_text)

Value: the number to format
Format_text: the format to apply

The example below shows the basic use of TEXT where the text “Rent amount is” has been appended to the amount in C2. The use of “General” as the second parameter indicates no specific formatting has been applied.

IBM Planning Analytics Tips & Tricks: Excel Text

To apply formatting, you can replace the second parameter with a specific format string.

The easiest way to find format strings is to get them from the Format Cells dialog box.

In Excel, press CTRL+1
Go to the Number tab
Select Custom in the Category pane
You can then copy the format string from the “Type” textbox

Top Three IBM Planning Analytics Tips & Tricks Posts – 2023

December 19, 2023 by Revelwood

What were your favorite IBM Planning Analytics Tips and Tricks blog posts this year? We took a look at our website analytics and found the top three posts of 2023 are (drumroll, please!) …

IBM Planning Analytics Tips & Tricks: DefineCalc

IBM Planning Analytics for Excel (PAfE) version 80 introduced a new function called “DefineCalc.” This function allows a user to create a custom calculation using TM1/Planning Analytics data via the use of MDX.

IBM Planning Analytics Tips & Tricks: Expanding Sections of Stacked Dimensions

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.

IBM Planning Analytics Tips & Tricks PAW Users and Groups

Do you know how to manage your users and groups in IBM Planning Analytics Workspace (PAW)? As of version 84, PAW now allows you to edit this information via a single click.

IBM Planning Analytics Tips & Tricks: Using the Subset Editor in Planning Analytics Workspace

December 12, 2023 by Revelwood

In the past, manipulating subsets in IBM Planning Analytics Workspace (PAW) involved a convoluted process of copying, creating new sets, and pasting elements. The subset editor solves this problem. The subset editor enables you to perform multiple tasks with a single click.

Watch this short IBM Planning Analytics Tips & Tricks video to see Lee Lazarow, Revelwood’s IBM Planning Analytics practice leader, demonstrate how to easily grab an element, its children, siblings, parents and descendants.

Previously, manipulating subsets focused on the left side of navigation. Inserting elements or expanding to children or descendants was confined to this area, creating limitations in functionality. Planning Analytics, version 88, extended the subset editor’s manipulation functionality. Now users can easily expand or keep elements with a single click!

By right-clicking on an element, users can choose between options like “expand to” or “keep with.” This newfound flexibility allows for expanding to descendants, ancestors, parents, siblings, or any combination, all with a single click.

This new functionality makes the process of manipulating subsets more intuitive and efficient. You can easily work with children, siblings, parents, or any combination of them.

IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace (PAW) Region Expansion

November 28, 2023 by Lee Lazarow

A while back I wrote a blog about regions within regions in IBM Planning Analytics. This approach allows you to make your rules easier to navigate and read when scrolling through many lines of code.

As you create regions, you can expand and contract lines of code by clicking the arrow next to the applicable lines. Here is an example of a contracted region (line 1) and an expanded region (line 6):

Text

Description automatically generated

But did you know that Planning Analytics Workspace allows you to expand or contract all regions at the same time? The workbench ribbon contains icons to expand (called “unfold”) and contract (called “Fold”) all regions with a single click.The left icon below is used for expansion and the right icon below is used for contraction.

Here is an example of 15 lines of code with the use of regions.

Text

Description automatically generated with medium confidence

This approach will allow you to reduce your screen footprint and make it even easier to quickly navigate through your code.

IBM Planning Analytics Tips & Tricks: How to Determine when Users Logged in to Planning Analytics Workspace

November 14, 2023 by Revelwood

Have you ever wanted to learn when a user last logged in to IBM Planning Analytics Workspace (PAW)? Or to generate a list of dates and times when users logged in?

IBM Planning Analytics version 87 includes a feature to easily figure out the last login time of your PAW users.

Watch this short video from Lee Lazarow, Revelwood’s Planning Analytics practice lead. You’ll see Lee demonstrate how to:

Find ‘Users and Groups’, which contains the date and time of logins.
Download that information into a CSV file you can save
Learn the roles, dates and times of logins

This approach gives us the ability to quickly create a list of all of our users and for each one of our users, a date and time associated with the last login.

IBM Planning Analytics Tips & Tricks: Excel Substitute Function

October 31, 2023 by Ivan Cepero

Are you familiar with the SUBSTITUTE function in Excel? The SUBSTITUTE function in Excel replaces one value with another. This is similar to the REPLACE function, but SUBSTITUTE provides more flexibility.

The syntax of the SUBSTITUTE function is:

=SUBSTITUTE(text,old_text,new_text,instance_num)

Text: This is the text to be evaluated and changed
Old_text: This is the subtext to be replaced.
New_text: This will be used as the replacement.
Instance_num (optional): This defines which occurrence of old_text to replace. If this value is blank then all occurrences of old_text will be replaced.

Here are two examples of the substitute function:

Example 1:

This formula removes the second dash from the account string.

Graphical user interface, application

Description automatically generated

Example 2:

To count the number of dashes in a cell, this formula subtracts the length of the cell without dashes from the original length of the cell.

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:

IBM Planning Analytics Tips & Tricks: PAW Pie Chart Total

Read more about visualizations in IBM Planning Analytics:

IBM Planning Analytics Tips & Tricks: Excel TEXT Function

Read more Excel Tips & Tricks:

Top Three IBM Planning Analytics Tips & Tricks Posts – 2023

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Using the Subset Editor in Planning Analytics Workspace

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace (PAW) Region Expansion

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: How to Determine when Users Logged in to Planning Analytics Workspace

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel Substitute Function

Example 1:

Example 2:

Read more IBM Planning Analytics Tips & Tricks:

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

TM1

Read more about visualizations in IBM Planning Analytics:

Read more about visualizations in IBM Planning Analytics:

Read more about visualizations in IBM Planning Analytics:

Read more Excel 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:

Example 1:

Example 2:

Read more IBM Planning Analytics Tips & Tricks:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT