Revelwood

Your SUPER-powered WP Engine Site

IBM Cognos TM1

IBM Planning Analytics Tips & Tricks: Excel TEXTSPLIT

by

Have you ever needed to parse a text string into different columns in Excel? This is easy to do via the text-to-columns wizard, but what if you wanted to do this via a formula? The TEXTSPLIT function allows you to split a text value across columns. It even lets you split values across rows!  

The function has two required parameters:

=TEXTSPLIT(text,col_delimiter)

  • The text parameter defines the string value you are searching for
  • The column delimiter parameter defines the character(s) used for each split

In addition, there are optional parameters that can be used to determine case sensitivity, determine a row delimiter, and define what to do if two matches in a row are found

The following example shows how to use the function by searching for a space between each word and then putting each word into a new column. The source value is in cell A1 and a single TEXTSPLIT formula has been created in cell A2 (note: the text of the formula has been replicated onto cell A6 to show it in this image).

Graphical user interface, application, table, Excel Description automatically generated

This next example shows a similar concept but uses a different parameter to instead put each word into a new row.

Table Description automatically generated

This approach will simplify your searches by eliminating the need to create multiple functions to parse the same set of data.

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel TEXTBEFORE & TEXTAFTER

IBM Planning Analytics Tips & Tricks: Excel SEQUENCE

IBM Planning Analytics Tips & Tricks: PAW Go To Line in Process

Home » IBM Cognos TM1 » Page 8

IBM Planning Analytics Tips & Tricks: Charts in Planning Analytics Workspace, Part 2

by

Did you know IBM Planning Analytics Workspace (PAW) gives you many options for showing data in charts? We recently shared a few tips and tricks for visualizing your data in charts. Here are some more.

Pie Chart Sizing

Did you know that PAW allows you to “split” your pie chart widget to define how much of the space is allocated to the legend and how much of the space is allocated to the chart? 

In this blog post, we explain how to view and modify the split. 

PAW Chart Multicolors

One of the visualizations in Planning Analytics is a column chart. This type of chart is a good way to compare items since all the lines are in proportion to each other.

Read this blog post to learn an easy way to create a column chart via an exploration. We then explain how to change that visualization into a column chart. 

Mekko Charts

PAW offers many visualization options to allow you to show and analyze your data in creative ways. This includes standard chart types such as bar, line and pie. But it also includes some reports that are not always understood. One of these report types is a Mekko chart.

A Mekko chart (also sometimes called a Marimekko chart) is used to extend a stacked column chart by using the width of each column to show the overall importance of that section. Read our blog post on using Mekko 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!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Charts in Planning Analytics Workspace, Part 1

IBM Planning Analytics Tips & Tricks: DefineCalc

IBM Planning Analytics Tips & Tricks: Top 3 Blog Posts of 2022

Home » IBM Cognos TM1 » Page 8

IBM Planning Analytics Tips & Tricks: Approaches in Planning Analytics’ New Set Editor

by

In Revelwood’s latest IBM Planning Analytics Tips & Tricks video, Lee Lazarow demonstrates three different ways to change a drop-down menu in Planning Analytics’ new set editor.

Watch this video to learn the three approaches:

1. Change the drop-down by doubling clicking the dimension, which opens the set editor

2. Click on the three dots on the right side to open the set editor

3. Click on the drop-down to see the elements currently in the set (note: you need to enable this when you are creating your Exploration)

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Watch more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: 2022’s Top 3 Tips & Tricks Videos

IBM Planning Analytics Tips & Tricks: How to Set Up Action Buttons in Planning Analytics for Excel

IBM Planning Analytics Tips & Tricks: How to Set Up Synchronizations in IBM Planning Analytics Workspace

Home » IBM Cognos TM1 » Page 8

IBM Planning Analytics Tips & Tricks: Charts in Planning Analytics Workspace, Part 1

by

Did you know IBM Planning Analytics Workspace (PAW) gives you many options for showing data in charts? Here are some tips and tricks for working with charts in PAW.

Scatter Chart vs Bubble Chart

A scatter chart is used to show relationships within your data. This type of chart is great to see patterns and groupings over a large set of values. A bubble chart is also used to show relationships and it is also great to see patterns. So what is the difference between the two types of charts? 

In this blog post, we explain the difference between the two types of charts. As you’ll learn, the type of chart makes a difference when reviewing your data. How to decide which chart to use? It’s simple – ask yourself how many details you want to see at once. 

The Waterfall Chart

PAW includes various visualizations that can be used to view your data. One of these visualizations entails a chart called a waterfall chart. This type of chart will help you show the components of a summarized number. It is very helpful in determining which values are beneficial to the total and which values are detrimental.

This blog post shows an exploration in PAW and its corresponding waterfall chart. 

Lines vs Area for Multiple Elements

A line chart is typically used to show values over time. This type of chart is great to see trends with your data. An area chart is also used to show values over time and is sometimes referenced as a line chart with the area below the line filled. While this is the case in PAW for a chart that contains a single line, it is not the case for a chart with multiple lines. 

Read this blog post to learn about a line chart versus an area chart. 

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!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: DefineCalc

IBM Planning Analytics Tips & Tricks: Top 3 Blog Posts of 2022

IBM Planning Analytics Tips & Tricks: 2022’s Top 3 Tips & Tricks Videos

Home » IBM Cognos TM1 » Page 8

IBM Planning Analytics Tips & Tricks: DefineCalc

by

IBM Planning Analytics for Excel (PAx) 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.

There are a series of parameters, and the key ones are:

  • sDataSource – The connection URL used when adding your connection to Pax.  You can enter “*”if you would like the calculation to apply to all data sources.
IBM Planning Analytics Tips: DefineCalc
  • sServerName = Your TM1/PA server name.  You can enter “*” if you would like the calculation to apply to all servers.
  • sCalcMUN = The name for the calculation.  This will be referenced in your DBRW formulas.
  • sExpression = The MDX expression being used for the calculation (note: if you would like to remove a custom calculation then set this variable to be blank).

One use case for this function is to grab the total value of a subset. In the example below, we have a report that uses a subset called “DefineCalc Example”:

IBM Planning Analytics Tips & Tricks: DefineCalc
IBM Planning Analytics Tricks: DefineCalc

The following DefineCalc formula was used to create a total:

IBM Planning Analytics Tips & Tricks: DefineCalc

Now, let’s break this apart by each parameter:

  • sDataSource – “*”
    • This will apply to all data sources (if multiple)
  • sServerName = “*”
    • This will apply to all TM1/PA servers (if multiple)
  • sCalcMUN = “[bpmAccount].[Example 1]”
    • This first half of this value defines the bpmAccount dimension
    • The second half of this value consists of a unique name within the bpmAccount dimension
  • sExpression = “AGGREGATE(TM1SubsetToSet([bpmAccount],’DefineCalc Example’))”
    • This is an MDX expression that will give the total of the “DefineCalc Example” subset within the bpmAccount dimension

The result of this formula is populated in the cell as “Example 1.”

IBM Planning Analytics Tips & Tricks: DefineCalc

We can now reference “Example 1” within the DBRW formula as an element in the bpmAccount dimension.

IBM Planning Analytics Tips & Tricks: DefineCalc

The =Sum formula of the three data rows matches the DBRW that references the DefineCalc function.

IBM Planning Analytics Tips & Tricks: DefineCalc

This shows one common use case for the new DefineCalc function, but the use MDX allows for countless other applications!

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!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: MDX Syntax Explained

IBM Planning Analytics Tips & Tricks: Dynamic Subsets Based on a Cube

IBM Planning Analytics Tips & Tricks: TM1Ellist

Home » IBM Cognos TM1 » Page 8

IBM Planning Analytics Tips & Tricks: Top 3 Blog Posts of 2022

by

What was your favorite IBM Planning Analytics Tips & Tricks blog post in 2022? We did some analysis and determined the three most popular PA Tips & Tricks blog posts. They are:

1. IBM Planning Analytics Tips & Tricks: Dynamic Subsets Based on a Cube

Read Dillon Rossman’s blog post to learn how to use MDX to create dynamic subsets based on cube values.

2. IBM Planning Analytics Tips & Tricks: Adding a New Entry to Index Cube via a Dynamic Report

This post presents an interesting situation. If your Planning Analytics model contains a cube that uses an index or slot dimension, you will need to create a template that allows your users to add data to an unused index.

3. IBM Planning Analytics Tips & Tricks: Pax – Rebuild Book vs Recreate Book

In this post, Lee Lazarow explains the difference between the “Rebuild Book” button and the “Recreate Book” button in Planning Analytics for Excel (Pax).

Are you receiving our IBM Planning Analytics Tips & Tricks every Tuesday? If not, sign up to subscribe to our newsletter. You’ll get the latest post direct to your inbox.

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!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: 2022’s Top 3 Tips & Tricks Videos

IBM Planning Analytics Tips & Tricks: Excel TEXTBEFORE & TEXTAFTER

IBM Planning Analytics Tips & Tricks: How to Set Up Action Buttons in Planning Analytics for Excel

Home » IBM Cognos TM1 » Page 8

IBM Planning Analytics Tips & Tricks: 2022’s Top 3 Tips & Tricks Videos

by

The Revelwood team has created many IBM Planning Analytics Tips & Tricks videos. Which ones made it into the top 3 with the most views on YouTube? [Drumroll please!]

They are (in order of the most views):

1. Shortcuts in PAW Models

In this video, we demonstrate how to save time when creating dimensions, cubes, processes and chores. We explain how to use a shortcut to create the components in your model. In legacy tools, such as Architect, you had to create these components piece by piece. With Planning Analytics you can create multiple components without having to navigate back and forth.

2. Color in Planning Analytics Workspace Charts

This video shows you how to add color to your multidimensional charts in IBM Planning Analytics Workspace (PAW). We demonstrate how to do this using an Exploration. You’ll learn how to easily add/change colors to make your charts easier to read and understand.

3. Filtering in Planning Analytics Editor

In this video, we demonstrate some approaches to filtering your elements when defining a subset in the Planning Analytics editor. You’ll learn how to:

  • Filter dimensions
  • Use descriptors such as “contains,” “equal,” and “start.” This eliminates the need to use *ZYX.
  • Filter elements
  • Reset filters
  • Add a second filter

Want to learn more? Take a look at all our IBM Planning Analytics Tips & Tricks videos. Get notified when we have a new video by subscribing to our Youtube channel.

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Watch more IBM Planning Analytics Tips & Tricks Videos:

IBM Planning Analytics Tips & Tricks: How to Set Up Action Buttons in Planning Analytics for Excel

IBM Planning Analytics Tips & Tricks: How to Set Up Synchronizations in IBM Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Global Settings in Overview Area of Exploration

Home » IBM Cognos TM1 » Page 8

IBM Planning Analytics Tips & Tricks: Excel TEXTBEFORE & TEXTAFTER

by

A while back, I wrote a blog about using Excel to manipulate text. The functions in that blog allow you to rip text apart and put the pieces together so you can parse out pieces of your text strings. Since that writing, Microsoft has introduced two additional functions that can help you search for specific text values. These functions are TEXTBEFORE and TEXTAFTER.

  • TEXTBEFORE will return text before the characters you are searching for
  • TEXTAFTER will return text after the characters you are searching for

Both functions have two required parameters:

=TEXTBEFORE(text,delimiter)

=TEXTAFTER(text,delimiter)

  • The text parameter defines the string value you are searching for
  • The delimiter parameter defines the point after you want to extract

In addition, there are optional parameters that can be used to determine case sensitivity and what to do if no match if found.

The following example shows how to use these functions:

Graphical user interface, text, application, chat or text message Description automatically generated

This approach will simplify your searches by eliminating the need to merge functions such as FIND, and LEN and LEFT/RIGHT together.

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: How to Set Up Action Buttons in Planning Analytics for Excel

IBM Planning Analytics Tips & Tricks: Excel SEQUENCE

IBM Planning Analytics Tips & Tricks: PAW Go To Line in Process

Home » IBM Cognos TM1 » Page 8

IBM Planning Analytics Tips & Tricks: How to Set Up Action Buttons in Planning Analytics for Excel

by

Revelwood’s latest IBM Planning Analytics Tips & Tricks video features Dillon Rossman, a consultant with our IBM Planning Analytics practice, demonstrating how to set up action buttons in IBM Planning Analytics for Excel (Pax).

In this video, Dillon shows your three approaches: 

  • Default approach
  • Creating the action button in a different workbook or sheet
  • Run a process and then go to another worksheet

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Watch more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: How to Set Up Synchronizations in IBM Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Filtering in IBM Planning Analytics Editor

IBM Planning Analytics Tips & Tricks: Global Settings in Overview Area of Exploration

Home » IBM Cognos TM1 » Page 8