IBM Cognos TM1

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

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: Cell Security in Planning Analytics Workspace

October 17, 2023 by Revelwood

In our latest IBM Planning Analytics tips & tricks video, Lee Lazarow, Revelwood’s IBM Planning Analytics practice lead, explains and demonstrates cell security in IBM Planning Analytics (PAW). Cell security is a critical aspect of data management, providing control access to specific data points within PAW cubes.

Watch this video and learn how to:

Determine the cube you want to add security to
Choose the dimensions
Create the cell security
Define security manually
Automate security with rules
Save and apply rules
Make modifications

In conclusion, cell security in IBM Planning Analytics is a powerful tool for managing data access. By following these steps, you can efficiently define and implement security measures that align with your specific needs. PAW offers a versatile platform for ensuring data integrity, whether through manual adjustments or automated rules.

IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace (PAW) Process Error Logs

September 26, 2023 by Lee Lazarow

Do you know how to use IBM Planning Analytics Workspace (PAW) to access error logs from your TurboIntegrator (TI) processes? It’s as simple as a single click!

To access your logs, right-click on the Processes node in the workbench and select the option for Error logs.

Once selected, a popup window will appear with a list of the existing logs. This window allows you to sort the list by process name or by date created.

There are two ways to view a log file:

Click on the box to the left of the process name
Click on the three vertical dots to the right of the date

Both methods will then let you view the log file or download the file.

This feature, when combined with PAW’s development tools, will allow you to fully develop and debug TurboIntegrator scripts using the PAW interface.

IBM Planning Analytics Tips & Tricks: Adding Images to Charts

September 12, 2023 by Ivan Cepero

Charts are an excellent way to use visuals to convey information. Did you know that you can increase the effect of charts in Excel by adding custom images?

Consider this plain pie chart.

Chart, pie chart

Description automatically generated

We can insert a corporate logo to give the chart a greater visual appeal and branding.

1 – Click on the chart to select it

2 – Right click and select “Format Chart Area”

3 – Select “Picture or texture fill”

A screenshot of a phone

Description automatically generated with medium confidence

4 – Select “Insert” to insert an image from your computer or “Clipboard” to insert a previously copied image

When clicking “Insert” you will see a pop up letting you pick various image sources.

5 – You can further refine the options by having the image tile, offset or scale.

Here is the updated chart with the Revelwood logo applied, tiled and set with transparency level 80%:

You can also apply custom images to a specific data point (e.g. a pie slice in this example) by initially selecting the data point instead of the entire chart, then following the steps outlined above.

IBM Planning Analytics Tips & Tricks: Counting Hidden Rows in Excel

August 22, 2023 by Ivan Cepero

With a clever combination of formulas, you can have Excel calculate the number of hidden rows in a data set. This trick will work on both manually hidden rows and autotfilter hidden rows.

There are two ways to count hidden rows, depending on how your data is structured. Both methods use the AGGREGATE function.

The syntax of AGGREGATE is:

=AGGREGATE(function_num,options,array)

Where

Function_num is one of the following:

Function_num	Function
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV.S
8	STDEV.P
9	SUM
10	VAR.S
11	VAR.P
12	MEDIAN
13	MODE.SNGL
14	LARGE
15	SMALL
16	PERCENTILE.INC
17	QUARTILE.INC
18	PERCENTILE.EXC
19	QUARTILE.EXC

And options are one of the following:

Option	Behavior
Default	Ignore nested SUBTOTAL and AGGREGATE functions
1	Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2	Ignore error values, nested SUBTOTAL and AGGREGATE functions
3	Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4	Ignore nothing
5	Ignore hidden rows
6	Ignore error values
7	Ignore hidden rows and error values

We will be leveraging option 5: Ignore hidden rows

Consider the example data set:

Application, table, Excel

Description automatically generated

Blank cells merit special treatment and will determine which method you use.

No Blanks in Data

If your data will have no blank (zeros are ok) like column D in the sample data set, you can use this formula to count the hidden rows:

=AGGREGATE(2,4,Table1[Feb])-AGGREGATE(2,5,Table1[Feb])

Blanks in Data

Table

Description automatically generated

If there are blanks in the data (as in column C), you will need to add a helper column in Excel. The helper column will use AGGREGATE for the cell in the same row and this converts a blank into a 0. Use the same formula as the first method but use the helper column instead of the original data.

=AGGREGATE(2,4,G3:G7)-AGGREGATE(2,5,G3:G7)

IBM Planning Analytics Tips & Tricks: PAW Pie Chart Total

Read more about visualizations in IBM Planning Analytics:

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: Cell Security in Planning Analytics Workspace

Watch this video and learn how to:

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace (PAW) Process Error Logs

There are two ways to view a log file:

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Adding Images to Charts

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Counting Hidden Rows in Excel

No Blanks in Data

Blanks in Data

Read more IBM Planning Analytics Tips & Tricks:

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

IBM Cognos TM1

Read more about visualizations in IBM Planning Analytics:

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 this video and learn how to:

Read more IBM Planning Analytics Tips & Tricks:

There are two ways to view a log file:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

No Blanks in Data

Blanks in Data

Read more IBM Planning Analytics Tips & Tricks:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT