IBM Planning Analytics

IBM Planning Analytics Tips and Tricks: Upgrading to Planning Analytics for Excel 2.0.65 or later

March 21, 2023 by Marc Assenza

As of version IBM Planning Analytics version 2.0.65, you can choose to either launch IBM Planning Analytics for Excel (PAx) as a single session (by double-clicking the .XLL file) or load it as an add-in (and have it available for all Excel sessions).

To install the latest version of PAx, first uninstall any version prior to 2.0.65. Once completed, download the.xll file associated with the version you want to install. Be sure to use a file that correlates with either your 32-bit version of Microsoft Excel or your 64-bit version of Microsoft Excel. You can check which version of Excel you have by clicking on File, Account:

A picture containing table

Description automatically generated

Within that menu, click on the link that reads “About Excel.”

Text

Description automatically generated

Details about your version will then appear, including the bit version. Here is an example of what you may see:

A picture containing rectangle

Description automatically generated

Once downloaded, you can configure the .xll file as an Excel add-in or you can simply double-click the file to run PAx. I prefer to launch the .xll file individually for a single session of Excel and not allow it to persist across all my Excel sessions. Therefore, I created a folder named “PAfe” and placed the .xll file in the folder. I then created a shortcut from my desktop pointing to the file. This approach allows me to perform future upgrades by simply replacing a single file!

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!

IBM Planning Analytics Tips & Tricks: Excel YEARFRAC

March 14, 2023 by Lee Lazarow

I often build models that define the fraction of a year for a given month. For example, January is approx. 8.5% of a calendar year (31/365) and September is approx. 11.5% of a working day’s calendar (30/260). But what happens in situations when you want to calculate the percentage of a year where the days are not the first or last day of the month? This is where Excel’s YEARFRAC formula can help.

The YEARFRAC function calculates the fraction of a year based on the number of whole days between two dates. Maybe you have summer employees and want to determine how much of the calendar year they will be employed or maybe you need to rent some equipment and pay based on an annual rate.

The function has two required parameters and one optional parameter:

=YEARFRAC(start_date, end_date, [basis])

Start_date (required)
- Microsoft recommends using the DATE function for this value
End_date (required)
- Microsoft recommends using the DATE function for this value
Basis (optional)
- This determines the denominator using a series of approaches such as 360 vs 365 and whether you want to include the start date

Application, table, Excel

Description automatically generated

This function will allow you to quickly calculate decimals which can then be used for various forms of allocations, seasonality, and other types of spreads.

IBM Planning Analytics Tips & Tricks: On-Demand Webinars, Part 1

March 7, 2023 by Revelwood

So many webinars, so little time! If you are like me, you probably receive invitations to webinars on a regular basis. Some of them even make it onto my calendar. Unfortunately, more often than not, I have the best intentions of attending a webinar, but then can’t make the live event.

That’s what on-demand webinars are for!

Did you know Revelwood has a YouTube playlist of all our IBM Planning Analytics / TM1 webinars? To date, we have 27 webinars available for viewing when convenient for you.

Let’s take a look at two of our more popular webinars.

Best Practices When Using Planning Analytics Workspace Charts

Charts and graphs are an excellent way to illustrate relationships in data and to highlight trends. They are visualizations of data that allow end users to quickly analyze a large amount of data. To make the biggest impact, it is important to choose the right chart for the use case of the data.

Watch this on-demand webinar to learn about using the following charts in IBM Planning Analytics Workspace (PAW):

Area charts
Bar charts
Stacked bar charts
Column charts
Stacked column charts
Line charts
Map charts
Packed bubble charts
Pie charts
Point utilization charts
Radial charts

Creating PAW Charts Using the New Experience

The new experience in IBM Planning Analytics introduced new visualizations in PAW. In this webinar we review various methods to create a chart. We also offer recommendations on building dashboards and reporting pages.

During this webinar we explain and demonstrate:

The types of charts that are available and when to use each
How to create a chart from an existing exploration
How to create a chart from scratch
How to customize your charts
Recommendations when creating charts and dashboards

Learn More About Charts in IBM Planning Analytics

We have even more resources for you on creating charts in Planning Analytics! On Wednesday, March 15, 2023 at 1:00pm ET we will be hosting a live webinar on waterfall charts in Planning Analytics.

A waterfall chart is a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values.

Join Lee Lazarow and Terry Kerans to learn:

What a waterfall chart can tell you
When to use a waterfall chart
How to create a waterfall chart in PAW

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: Excel Tips, Part 2

February 28, 2023 by Revelwood

Revelwood’s IBM Planning Analytics team has created numerous tips & tricks focused on helping people use Excel. Over the next few weeks we’ll be highlighting some of the most popular and interesting posts on Excel. You can read part 1 here.

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

Have you ever wanted to define the number of working days between two calendar dates? Do you define “working days” simply as non-weekend days or do you also include holidays? Did you know that you can calculate this information using Excel’s NETWORKDAYS function?

The NETWORKDAYS function is used to determine the number of whole working days between start_date and end_date. This blog post explains how to use it.

IBM Planning Analytics Tips & Tricks: Excel TYPE Function

Have you ever referenced a list in Excel and needed to know whether each value was a number or a string? This may be required when another function depends on the type of value in each cell.

Excel has a function called TYPE that will give you this information. The function contains a single parameter, which is simply the cell you want to check. Learn more about using the TYPE function in this blog post.

IBM Planning Analytics Tips & Tricks: Excel OFFSET Function

Have you ever wanted to reference a cell based on its placement compared to another cell? Maybe you want to create a formula where each cell looks at the value 2 columns to the right or maybe you want to compare a value to the cell above. This information can be determined using Excel’s OFFSET function.

The OFFSET function is used to reference a range that is a specified number of rows and columns from a cell. We detail the syntax of the OFFSET function in this blog post.

IBM Planning Analytics Tips & Tricks: Excel EOMONTH

February 21, 2023 by Lee Lazarow

Thirty days has September …

Do you sing that out loud every time you want to determine the number of days in a month? If so, do you have to pause and think about the end when defining the last day of February? More importantly, do you have to write complex logic into your spreadsheet to define this? If so, you may want to learn more about the EOMONTH function in Excel!

The EOMONTH function is designed to tell you the last day of a month – either the current month, a future month, or a historical month. The function has two parameters:

=EOMONTH(start_date, months)

The start_date parameter defines the initial date of your calculation
- Microsoft recommends using the DATE function for this value
The months parameter defines the number of months from the start date
- A zero value results in the current month
- A positive value results in a future month
- A negative value results in a previous month

Graphical user interface, application, table, Excel

Description automatically generated

This approach will help you in various planning models such as …

Workforce planning – when to start calculating benefits
Asset planning – when to begin the depreciation calculations

This can also be used to define start dates by simply adding 1 to the result and determining the first day of the next month. Now we just need a formula to help us when someone obnoxiously tells us to “just perform the task on each day that ends in Y.”

IBM Planning Analytics Tips & Tricks: Excel Tips, Part 1

February 14, 2023 by Revelwood

IBM Planning Analytics Tips & Tricks: Excel MAXIFS and MINIFS

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?

Read this blog post to learn more about MAXIFS.

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

Have you ever wanted to filter a list of data to look at a subset of the entire table? Many of you know that you can use the filter button within Excel’s data section to do this.

But what if you wanted to create a filter that users can change without having to navigate through ribbons? Did you know that you can do this using Excel’s FILTER function?

Take a look at this blog post to learn how to use Excel’s FILTER function to do this.

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

Have you ever wanted to search a list and define the position where it appears in the list? Maybe you want to know how many sales reps exceeded their quota or want to define how many products cost more than an approved purchase price. This information can be calculated using Excel’s XMATCH function.

Read this blog post to learn how to use Excel’s XMATCH function.

IBM Planning Analytics Tips & Tricks: Excel TEXTSPLIT

February 7, 2023 by Lee Lazarow

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).

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.

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

January 31, 2023 by Revelwood

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 Tips & Tricks: How to Make an MDX Statement Dynamic

January 24, 2023 by Revelwood

Did you know we have a number of resources available to learn more about MDX? We hosted a two-part series on How to Use MDX in Your IBM Planning Analytics Series. We also published a blog post, IBM Planning Analytics Tips & Tricks: MDX Syntax Explained, to accompany part 1 of the webinar series.

In part 2 of our MDX webinar series, we demonstrate how to make an MDX dynamic by using the power of Excel.

You’ll see we start with a basic Excel report, generated out of IBM Planning Analytics for Excel (PAx). We then take the static, base MDX and turn it into a concatenation of parts of the index statement and the cell reference. In turn, that enables us to change the root account, thereby changing the data that shows in the report.

Watch the full webinar to learn more about working with MDX.

IBM Planning Analytics Tips and Tricks: Upgrading to Planning Analytics for Excel 2.0.65 or later

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel YEARFRAC

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: On-Demand Webinars, Part 1

Learn More About Charts in IBM Planning Analytics

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel Tips, Part 2

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel EOMONTH

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel Tips, Part 1

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel TEXTSPLIT

Read more IBM Planning Analytics Tips & Tricks:

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

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: How to Make an MDX Statement Dynamic

Read more IBM Planning Analytics Tips & Tricks on MDX:

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

IBM Planning Analytics

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Learn More About Charts 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:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks on MDX:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT