Revelwood

Your SUPER-powered WP Engine Site

IBM Planning Analytics

IBM Planning Analytics Workspace Settings: DIM Editor Page Size

by

Have you ever edited a large dimension in IBM Planning Analytics Workspace and found that you are forced to repeatedly scroll through a list to see the full dimension? This may be because you are only looking at 200 elements at a time.

Did you know that you can control how many elements initially load when the dimension editor is opened and each time a new page is refreshed? There are two ways to do this.  One approach is to manually define the setting within the dimension editor. This is done by clicking on the settings icon and modifying the option for Page size.  

The second approach is to always have this value defined. Users with the Administrator, Modeler, or Analyst role can use the Settings editor to define global settings. To modify this setting, click on your username at the top right corner of Planning Analytics Workspace, select the option for Profile and Settings, and then select the option for Settings. The page size definition is found within the Dimension Editor section. After modifying this setting, the value will be set by default within the dimension editor screen. Users can still manually change the option, if preferred.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Workspace Settings – Dim Editor Index

IBM Planning Analytics Tips & Tricks: Application Websheet Folders

IBM Planning Analytics Tips & Tricks: Rename Views in PAfE

Home » IBM Planning Analytics

IBM Planning Analytics Workspace Settings – Dim Editor Index

by

Have you ever looked at all elements in a dimension (via the “All” button in Perspectives or any of the prebuilt “All xxx” sets in IBM Planning Analytics) and wondered how the resulting order was defined? This is a result of a behind-the-scenes setting called an Index. Every element is assigned an index. It is used to define the default sorting / appearance of the list.

So how do you see the index number in PAW when editing a dimension? There are two ways to do this. One approach is to manually turn on the option within the dimension editor.  This is done by clicking on the settings icon and enabling the option for Element index.  

Once enabled, the index number will appear directly to the right of the element.

The second approach is to always have this setting enabled. Users with the Administrator, Modeler, or Analyst role can use the Settings editor to define global settings. To enable this setting, click on your username at the top right corner of Planning Analytics Workspace, select the option for Profile and Settings, and then select the option for Settings. The index definition is found within the Dimension Editor section. After enabling this setting, the Element Index option will be selected by default within the dimension editor screen. Users can still manually deselect the option, if preferred.

By viewing the index, it will be easy to see the order that elements will appear for various default sets.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Application Websheet Folders

IBM Planning Analytics Tips & Tricks: Rename Views in PAfE

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

Home » IBM Planning Analytics

IBM Planning Analytics Tips & Tricks: Application Websheet Folders

by

As you migrate from IBM Planning Analytics’ TM1Web to Planning Analytics Workspace (PAW), you may be asking yourself if you can still create folders to organize your websheets. The answer is yes!

Name change

Planning Analytics no longer refers to the folders as “Applications” since PAW applications have a different meaning. PAW now refers to the folders as “Websheets.”

Where to create and configure the folders

The folder creation and configuration is done with the Planning Analytics for Excel (PAfE) environment by using the same concepts as websheet creation:

  1. Go to the “Open” icon (next to the Publish icon)
  1. Create the new folder
  1. Once created, right-click on the folder to toggle between private and public (note: it will initially be set to private)

This approach will allow you to continue to group your websheets into an easy-to-use arrangement for your end users.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

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

IBM Planning Analytics Tips & Tricks: Update on Planning Analytics’ Deprecation

IBM Planning Analytics Tips & Tricks: Unpivot

Home » IBM Planning Analytics

IBM Planning Analytics Tips & Tricks: Rename Views in PAfE

by

Have you ever been in a situation where you saved a view and then realized that you want to change the name in IBM Planning Analytics for Excel (PAfE, formerly PAx)?  Maybe you had a typo or maybe you referenced a date that has changed.

In the old Perspectives world, there was no way to change the name of an existing view, so you were forced to resave the view with a new name and then delete the original view.  Planning Analytics for Excel offers the ability to rename a view without having to perform multiple steps.  

Renaming a view in PAfE is as simple as right-clicking on the view!

Once the option is selected, a popup box will appear that allows you to rename the view.  

This approach gives you the ability to maintain your environment with a single click, thereby simplifying your overall processes.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

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

IBM Planning Analytics Tips & Tricks: Update on Planning Analytics’ Deprecation

IBM Planning Analytics Tips & Tricks: Unpivot

Home » IBM Planning Analytics

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

by

Excel’s AGGREGATE function lets you manage complex reports and ensure they foot correctly.

For example, suppose you are building a sales report by product and region.

A screenshot of a table Description automatically generated

Building the Grand Total calculation for a multi-level report like this is error-prone. You would need to use the SUM function and select every individual region while ensuring that no totals were included.

You can use instead use the AGGREGATE function to define all the totals … both the subtotal and the grand total. AGGREGATE can sum (among other things) and you can set the option to ignore other AGGREGATE formulas.

The AGREGATE function requires three parameters:

  • A function number to define Average, Count, Max, Sum, etc.  Function number 9 is sum.
  • An option to define how to interact with other subtotals, hidden rows, etc.  Option 0 ignores other subtotals and aggregate functions.
  • The range to perform the function.

The image below shows the same report along with the formulas used.

A screenshot of a spreadsheet Description automatically generated

Notice how the formula in C17 can include all the cells above it. You can now insert rows in any region and not have to update the grand total.

Additionally, you can have AGGREGATE ignore hidden rows. Hide a particular SKU, see the results and unhide the row. Very easy for on the fly what-if scenarios. The ignore hidden rows feature works very well when trying to derive a sum of an Autofiltered list.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Unpivot

IBM Planning Analytics Tips & Tricks: PAW Export Data

A Resolution You Should Actually Keep: Upgrade Your IBM Planning Analytics Environment

Home » IBM Planning Analytics

IBM Planning Analytics Tips & Tricks: Update on Planning Analytics’ Deprecation

by

IBM recently made the End of Support dates for Planning Analytics 2.0.9.x official.

As we mentioned in our previous blog post on the deprecation of Planning Analytics 2.0.9.x, IBM is extending the general support period for Planning Analytics 2.0.9.x until October 31, 2025. This includes IBM Planning Analytics Local, IBM Planning Analytics Advanced and IBM Planning Analytics Express.

Now is the time for you to start thinking about your migration plan. The next ten months will pass by quickly.

Our team has created a few resources for you regarding the upcoming changes. They include:

We’re happy to work with you on your migration. Email us at info@revelwood.com.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Home » IBM Planning Analytics

IBM Planning Analytics Tips & Tricks: Unpivot

by

Did you know you can unpivot data in IBM Planning Analytics?

Sometimes you’ll find yourself in a situation where you need to change the layout of data from a grid format to a vertical table format, this is called unpivoting the data.

I’ll show you how to do this via some new Excel formulas.

Consider this sample data set:

A screenshot of a spreadsheet Description automatically generated

The goal is to have a table with three columns: Invoice, Month, Data. Here’s how to do that step-by-step.

Step 1

Use the EXPAND formula to create an array of Invoices.  In this case, we are creating a 5 x 12 array (5 rows x 12 columns).

=EXPAND($A$2:$A$6,5,12)

This will generate this:

A white grid with black and green text Description automatically generated

Step 2

EXPAND doesn’t know what to do with the other columns, so we can use IFERROR to fill in the errors with a specified value. In this case, if there’s an error then we want to replace the error with the first column value.

=IFERROR(EXPAND($A$2:$A$6,5,12),$A$2:$A$6)

Now we have this:

A screenshot of a computer Description automatically generated

Step 3

Now we use the TOCOL function to make the grid of invoice numbers into a column

=TOCOL(IFERROR(EXPAND($A$2:$A$6,5,12),$A$2:$A$6))

That generates:

A number of binary code Description automatically generated with medium confidence

Step 4

We repeat the previous steps for the months. We want an array of 12 x 5 for the months. The end formula is:

=TOCOL(IFERROR(EXPAND($B$1:$M$1,5),$B$1:$M$1))

Step 5

Use TOCOL on the data points to pivot them into a columnar format.

=TOCOL($B$2:$M$6)

Now we have a live, formula-based unpivot model that will refresh when the data is changed.

A screenshot of a spreadsheet Description automatically generated

Bonus Tip!

You can make the formulas more dynamic by using COUNTA instead of hard coding the number of rows/columns in the EXPAND function.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: PAW Export Data

A Resolution You Should Actually Keep: Upgrade Your IBM Planning Analytics Environment

Popular IBM Planning Analytics Videos of 2024

Home » IBM Planning Analytics

IBM Planning Analytics Tips & Tricks: PAW Export Data

by

While IBM Planning Analytics is an excellent database that allows you to easily slice and dice your data, there may be situations where you want to export data from the model.  Maybe you want to share information with someone who uses different software or maybe you want to use your data in another tool? PAW version 99 introduced the ability to export a data set from the PAW interface.

To export data, right-click on a cube within a modeling workbench, select the option to Import/Export and then select the option to Export Data.  

Once selected, you will be prompted to define the name of the .csv file that will be created.  You then can define the data to be exported by using an existing view and/or by defining the dimension details. For each dimension, you can define a hierarchy to use, a subset to use, or an element to use.

A screenshot of a computer Description automatically generated

You also have the ability to define filters for your numeric and string data (example: only show values greater than or equal to 100) and the ability to skip consolidated values, rule calculated values, and zero/blank values.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

A Resolution You Should Actually Keep: Upgrade Your IBM Planning Analytics Environment

Popular IBM Planning Analytics Videos of 2024

IBM Planning Analytics Tips & Tricks: PAW Export Dim Structure

Home » IBM Planning Analytics

A Resolution You Should Actually Keep: Upgrade Your IBM Planning Analytics Environment

by

Did you know that almost 80% of all New Year’s resolutions are associated with health?  As an IBM Planning Analytics user, it’s important to also think about your system’s health! This is why you should put “upgrading Planning Analytics” on your list of resolutions. Here’s why …

IBM recently announced some changing timelines to the forthcoming deprecation of Planning Analytics 2.0.9.x. For background, read our blog post, IBM Planning Analytics Tips & Tricks: Upcoming Changes to Planning Analytics, or watch our on-demand webinar, Understanding the Migration from Legacy TM1 to Planning Analytics. 

Key Updates

  • IBM is extending the general support period for Planning Analytics 2.0.9.x until October 31, 2025.
  • Users can purchase extended support from IBM for an additional year through October 31, 2026.
  • Planning Analytics 2.1.x is now supported until April 30, 2027.

Implications for Users

Staying on unsupported versions can expose organizations to security vulnerabilities, lack of technical support and missed opportunities for leveraging new functionalities. You should assess your current Planning Analytics environment and develop a roadmap for upgrading to supported versions.

  1. 1. Evaluate Current Usage: Understand how your organization uses Planning Analytics and identify any customizations or dependencies that may affect the upgrade process.
  2. 2. Plan the Upgrade: Develop a comprehensive plan that includes timelines, resource allocation and testing phases to ensure a smooth transition.
  3. 3. Engage with Revelwood: We can help you access support resources and documentation to assist with the upgrade process and address any challenges that come up.

By proactively addressing these end-of-support dates, organizations can maintain the integrity of their financial planning systems and continue to benefit from IBM’s advancements in Planning Analytics.Need help?

Send a quick note to: info@revelwood.com.

Home » IBM Planning Analytics