• Skip to main content
  • Skip to footer
Revelwood Logo

Revelwood

Your SUPER-powered WP Engine Site

  • Who We Are
    • About Us
      • Our Company
      • Our Team
      • Partners
    • Careers
      • Join Our Team
  • What We Do
    • Solutions
      • Workday Adaptive Planning
      • IBM Planning Analytics
      • BlackLine
    • Services
      • Implementation Services
      • Customer Care
        • Help Desk
        • System Administration as a Service
      • Training
        • Workday Adaptive Planning Training
        • IBM Planning Analytics / TM1 Training
    • Products
      • DataMaestro
      • LightSpeed
      • IBM Planning Analytics Utilities
  • How We Help
    • Use Cases
    • Client Success Stories
  • How We Think
    • Knowledge Center
    • Events
    • News
  • Contact Us

IBM Cognos TM1

IBM Planning Analytics Tips & Tricks: Excel DELET Function

July 26, 2022 by Lee Lazarow Leave a Comment

A while back I wrote a blog about the SCAN function which showed how you can search for specific characters and then combine this with the SUBST formula to create new sets of text. But what if you wanted an easier way to simply skip some characters within the string? This can be done with the DELET function.

The DELET function is used to delete a set of characters from within a string. The syntax of the command is: 

DELET (string, start, number)

  • String = the source text string
  • Start = the character where deletion will begin
  • Number = the number of characters to delete

For example: 

DELETE (‘phone’, 2, 3) returns ‘pe’

This function can be used to perform tasks such as removing dashes in between an element code number and name or removing a prefix from a list of elements. This will simplify your code by allowing you to simply ignore some text instead of merging two substrings 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: Using Charts and Data Series with Dynamic Reports

IBM Planning Analytics Tips & Tricks: Stacked Rows in PAx

IBM Planning Analytics Tips & Tricks: PASS Version Numbering

Home » IBM Cognos TM1 » Page 11

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: Budgeting Planning & Forecasting, enterprise performance management, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, TM1

IBM Planning Analytics Tips & Tricks: PAW Chart Show Totals

July 19, 2022 by Dillon Rossman Leave a Comment

Tips & Tricks

One of IBM Planning Analytics’ purposes is to serve as an aggregation tool. As a result, many of your explorations consist of a series of base level elements plus a total. Here is an example of a simple exploration that shows details of a list of companies plus a total:

Table

Description automatically generated

This exploration can easily be converted into a pie chart by simply changing the visualization to a pie chart. Here is what the pie chart will look like:

Chart, pie chart

Description automatically generated

However, the concept of aggregations has now shifted into a detriment since the total will always appear as 50% of the chart! Planning Analytics Workspace (PAW) allows you to hide the totals by defining properties within each field of your chart. You can do this via the following steps:

  1. While in Edit mode, single click on your pie chart widget.
  1. Select the option for “Fields” at the top, right corner. Not all visualizations use fields, so this may appear blank if you have not yet converted your widget to a pie chart.
  1. Click the three vertical dots associated with the dimension that should not include totals. In my example, the segment is associated with the company dimension.
  1. Select the option to Hide Totals.

Once selected, the same pie chart will look like this:

Chart, pie chart

Description automatically generated

This approach will allow you to properly define when you want a total to appear, thereby making your chart results beneficial to the user.

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: Displaying Percentages on a Planning Analytics Workspace (PAW) Pie Chart

IBM Planning Analytics Tips & Tricks: PAW Pie Percent or Value?

IBM Planning Analytics Tips & Tricks: PAW Explorations – Row & Column Headers

Home » IBM Cognos TM1 » Page 11

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: enterprise performance management, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, Planning Analytics Tips & Tricks, TM1

IBM Planning Analytics Tips & Tricks: Excel MAXIFS and MINIFS

July 12, 2022 by Lee Lazarow Leave a Comment

Many of you know that Excel’s MAX function will define the largest value within a range. And many of you also know that Excel’s SUMIFS function will allow you to merge IF checks into your sum formulas. But did you know that Excel has a function called MAXIFS which merges these two concepts together?

The MAXIFS function is used to determine the largest number within a range that satisfies one or more conditions.

The syntax of the function is:

=MAXIFS (max_range, criteria_range1, criteria1 [,criteria_range2, criteria2] [..])

  • Max_range
    • This is required
    • This defines the range to search
  • Criteria_range1
    • This is required
    • This defines the range to perform the IF check
  • Criteria1
    • This is required
    • This defines the criteria of the IF check
  • Criteria_range2 and Criteria2
    • These are optional
    • These allow you to have multiple IF checks
    • You can have more than 2 criteria

The following example shows how to define the maximum value for Lee.  

Other iterations of this formula include MINIFS, AVERAGEIFS and COUNTIFS.

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: Unhide Multiple Excel Sheets

IBM Planning Analytics Tips & Tricks: Refresh PAW Visualizations Automatically

IBM Planning Analytics Tips & Tricks: Improve Workbook Performance

Home » IBM Cognos TM1 » Page 11

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: enterprise performance management, IBM Cognos TM1, IBM Planning Analytics, Planning & Forecasting, Planning Analytics Tips & Tricks, TM1

IBM Planning Analytics Tips & Tricks: PAW Escape Character

July 5, 2022 by Tara Byrnes Leave a Comment

Have you ever been in a situation where you have an element name with an apostrophe (or two) in IBM Planning Analytics? Examples of this might entail last names such as “O’Connor” or “O’Sullivan”?  

Although IBM best practices specify that the apostrophe is a special character to avoid in object and element names, in many instances you’ll see an apostrophe used in your data source (especially in names and brand names).

Planning Analytics confuses the apostrophe as a single quote … which is a special character used to define literal strings. You can resolve this situation by using an “escape character.” An escape character invokes an alternative interpretation on the characters which follow.

The Planning Analytics escape character sequence defines 2 single quotes together as the equivalent of 1 single quote. 

Example:

‘O’’Connor’ will display as ‘O’Connor’  

This escape sequence can be used in both Rules and Turbo Integrator processes.  

This approach will allow your use of O’Connor to properly work instead of causing you to convert it into “oh darn”!

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’s XMATCH Function

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

IBM Planning Analytics Tips & Tricks: Excel Workbook Stats

Home » IBM Cognos TM1 » Page 11

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: Budgeting Planning & Forecasting, enterprise performance management, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, TM1

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

June 28, 2022 by Dillon Rossman Leave a Comment

Did you know you can use MDX to create dynamic subsets based on cube values in IBM Planning Analytics? In our example we will only show companies with net sales greater than 30,000,000 in the selected year. 

Graphical user interface, text, email

Description automatically generated

An MDX statement that can provide this result may look something like this:

{FILTER({TM1SUBSETALL([bpmCompany])},[bpmFinance].([bpmScenario].CurrentMember, [bpmDepartment].CurrentMember,[bpmAccount].[Net Sales],[bpmCurrency].CurrentMember,[bpmPeriod].CurrentMember,[bpmFinance_Msr].[Balance Post-Alloc]) > 30000000 )}

The components of this statement are:

  • FILTER({abc}),[Cube].([Dim1].[Value],[Dim2].[Value]…) > 30000000 )
    • Filter set ABC to only show members with a value greater than 30,000,000 for the intersection defined in the section after the comma
  • TM1SUBSETALL([bpmCompany])
    • Grab all members within the bpmCompany dimension
  • [bpmFinance]
    • The cube being used for the data
  • ([bpmScenario].CurrentMember, [bpmDepartment].CurrentMember,[bpmAccount].[Net Sales],[bpmCurrency].CurrentMember,[bpmPeriod].CurrentMember,[bpmFinance_Msr].[Balance Post-Alloc])
    • The intersection used to get the cube value
    • This is like a DBRW formula in the sense that we are defining a view by listing every dimension and providing a member within that dimension
      • CurrentMember will grab whatever element is selected in the cube view for that dimension
      • The other dimensions are using a specific member that we always want to filter by

Below you will see only companies with net sales greater than 30,000,000 for the “Balance Post-Alloc” measure remain in the bpmCompany subset. This will dynamically update as selections for other dimensions are changed.

Graphical user interface, text, application, email

Description automatically generated

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

IBM Planning Analytics Tips & Tricks: WildcardFileSearch

IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace Visualization Axis

Home » IBM Cognos TM1 » Page 11

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, IBM Planning Analytics Tips & Tricks, TM1

IBM Planning Analytics Tips & Tricks: Excel CubeDimensionCountGet Function

June 21, 2022 by Lee Lazarow Leave a Comment

Have you ever written a TurboIntegrator process and needed to know how many dimensions are in a specific cube? Maybe you want to loop through each dimension to create a zero out view. You can do this by using the TABDIM function and searching for the first blank result, or you can simply use the CubeDimensionCountGet function.

The syntax of the function is: 

CubeDimensionCountGet (CubeName)

This function will result in a numeric value that tells the number of dimensions within the cube.

Other uses of this code will allow you to define the number of arguments needed for a CELLPUT command, compare the number of dimensions between multiple cubes, or replicate an existing cube.

This approach will simplify your code by eliminating the need to create a loop.  

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 OFFSET Function

IBM Planning Analytics Tips & Tricks: PAx – Rebuild Book vs Recreate Book

IBM Planning Analytics Tips & Tricks: Updating ODBC Connections for Multiple Processes

Home » IBM Cognos TM1 » Page 11

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: enterprise performance management, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, Planning Analytics Tips & Tricks, TM1

IBM Planning Analytics Tips & Tricks: PAW Chart Multicolors

June 14, 2022 by Revelwood Leave a Comment

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.

An easy way to create a column chart is to simply create an exploration and change the visualization into a column chart. Here is an example of a stacked exploration that compares companies by scenarios and the related column chart:

Chart, histogram

Description automatically generated

This chart is great since it’s easy to compare the high and low values. However, it’s very hard to differentiate the sub-categories since all columns are the same color. In this case, it’s hard to compare Actuals vs. Final Budget.

You can configure your chart to use multiple colors via the following steps:

  1. While in Edit mode, single click on your chart widget.
  1. Select the option for “Fields” at the top, right corner. Not all visualizations use fields, so this may appear blank if you have not yet converted your widget to a chart.
  1. Move the applicable dimension from the Length category into the Color category. In my example, color is associated with the scenario dimension.
Graphical user interface, text, application, chat or text message

Description automatically generated

Once moved, the same chart will look like this:

Chart, bar chart

Description automatically generated

This approach will allow you to use your color palettes (defined within the properties) to differentiate the dimensions within your stacked column chart and make your analyses easier.

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: PAW Visualization Value Labels

IBM Planning Analytics Tips & Tricks: Refresh PAW Visualizations Automatically

IBM Planning Analytics Tips & Tricks: PAW Maps

Home » IBM Cognos TM1 » Page 11

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: enterprise performance management, Excel functions, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, Planning & Forecasting, Planning Analytics Tips & Tricks, TM1

IBM Planning Analytics Tips & Tricks: NumberToStringEx

June 7, 2022 by Revelwood Leave a Comment

IBM Planning Analytics has the ability to export data to flat files with the use of the ASCIIOUTPUT and TEXTOUTPUT functions. However, these two functions will only accept string variables. That means that numeric data will first need to be converted to string before exporting. Most developers know about the NumberToString function, which converts numbers to string, but there’s another function that is more powerful.

NumberToString does not apply any formatting to the numbers. This will work fine if the export is meant to be consumed by another system. However, if your export has an audience, you may want your report to have consistent and pleasing formatting. Here’s where the NumberToStringEx function can come in handy.

The NumberToStringEx function has four parameters: Value, Number Format, Decimal Separator, and Thousands Separator. The number format uses the same structures as the custom formatting in Excel.  Here’s an example of the function with the results:

NumberToStringEx( 1750.34892, ‘$#,0.00’, ‘.’, ‘,’)
$1,750.35

As you can see, the NumberToStringEx function eliminates the need to round your numbers prior to conversion as well as concatenating special characters after conversion.  

NumberToStringEx also allows for dual formatting of positive and negative numbers. Just like in Excel, you will need to separate the two using a semicolon. This eliminates any IF conditions you’ll need to use to format numbers if you had used the NumberToString function.

NumberToStringEx( -1750.34892, ‘$#,0.00;($#,0.00)‘, ‘.’, ‘,’)
($1,750.35) 

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: Planning Analytics Workspace Process Editor Function Help

IBM Planning Analytics Tips & Tricks: Excel OFFSET Function

IBM Planning Analytics Tips & Tricks: The SCAN Function

Home » IBM Cognos TM1 » Page 11

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: enterprise performance management, Excel functions, IBM Cognos TM1, IBM Planning Analytics, Planning Analytics Tips & Tricks, TM1

IBM Planning Analytics Tips & Tricks: WildcardFileSearch

May 31, 2022 by Revelwood Leave a Comment

Depending on your business process, you may need to load similar data from multiple files. One such example is loading headcount from different HR systems. This may be necessary if your company has gone through a merger. Another example is if you have forecast data coming from several managers in operations that don’t have access to IBM Planning Analytics. Instead of manually combining the files, you can use the WildcardFileSearch TurboIntegrator function to easily loop through all of the files.

The WildcardFileSearch function allows TurboIntegrator processes to search for files within a folder.  WildcardFileSearch requires 2 parameters: Path and PriorFileName. The Path can include ‘?’ and ‘*’ as wildcard characters. The ‘?’ is the wildcard for 1 character while ‘*’ is the wildcard for one or more characters. The PriorFileName tells the function were to begin the search. If this parameter is blank, the search begins with the first file in the Path. Below is an example of this function:

WILDCARDFILESEARCH( ‘C:\TM1Server\Files\TM1_*.csv’, ‘TM1_20220301.csv’)

In this example, the function will return the first filename that begins with “TM1_” and ends in “.csv” in the “C:\TM1Server\Files\” folder that occurs after the “TM1_20220301.csv” file.

IBM Planning Analytics, powered by TM1, is full of new features and functionality. Need advice? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. We post new Planning Analytics Tips & Tricks weekly in our Knowledge Center and in newsletters!

Read More IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Converting an Alias Back to the Element Name in TurboIntegrator

IBM Planning Analytics Tips & Tricks: Updating ODBC Connections for Multiple Processes

IBM Planning Analytics Tips & Tricks: FILL Function

Home » IBM Cognos TM1 » Page 11

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: Financial Performance Management, IBM Cognos TM1, Wildcardsearch

  • « Go to Previous Page
  • Page 1
  • Interim pages omitted …
  • Page 9
  • Page 10
  • Page 11
  • Page 12
  • Page 13
  • Interim pages omitted …
  • Page 24
  • Go to Next Page »

Footer

Revelwood Overview

Revelwood helps finance organizations close, consolidate, plan, monitor and analyze business performance. As experts in solutions for the Office of Finance, we partner with best-in-breed software companies by applying best practices guidance and our pre-configured applications to help businesses achieve their full potential.

EXPERTISE

  • Workday Adaptive Planning
  • IBM Planning Analytics
  • BlackLine

ABOUT

  • Who We Are
  • What We Do
  • How We Help
  • How We Think
  • Privacy

CONNECT

World Headquarters

Florham Park, NJ | 201 984 3030

European Headquarters

London & Edinburgh | +44 (0)131 240 3866

Latin America Office

Miami, FL | 201 987 4198

Email
info@revelwood.com

Copyright © 2025 · Revelwood Inc. All rights reserved. Revelwood® and the Revelwood logo are registered marks of Revelwood Inc.