Planning & Reporting

Workday Adaptive Planning Tips & Tricks: Using Display Fields in Modeled Sheets

October 26, 2023 by Robert Nordhagen

Modeled sheets in Workday Adaptive Planning often have no monthly data that displays on the sheet. To see the monthly calculations you must go to row details and create a report or create a separate sheet (standard or cube). However, you can see the calculated amounts in the sheet display by using the Display Column feature.

In this demo data, we see a typical Personnel modeled sheet. Notice there are no months. None of the calculated accounts such as Salary, FICA and Bonus are shown in a monthly display.

A screenshot of a computer

Description automatically generated

However, the Salary column is shown and is the Salary that was loaded. Assume this file was loaded in January 2023 and now it is August 2023. We can go to the columns definition of the modeled sheet editor and select Display Columns:

Which displays all the calculated accounts in a selection:

Bring over Personnel Salary and we see the following options:

Leaving the default options, we refresh the modeled sheet display to see a new field, Personnel Salary:

Personnel Salary is showing the monthly salary cost. Notice for those making $120,000, the new field is displaying $10,000 for the monthly salary. We can change the parameter, “Number of time periods in Rollup Range” from 1 to 12 as shown:

And the result is the monthly salary is now displaying the annual salary:

To see how the different parameters affect the display consider the following scenarios.

1. Display 1 Year before Reference Time Period
2. Display Year Containing Reference Time Period
3. Display 1 year after Reference Time Period

To see the current time period, set the parameter and see the result:

To see the 1 year later time period, set the parameter:

A screenshot of a computer screen

Description automatically generated

Note: the Start of plan is Jul 2023. So, Year After Reference Time Period is Jan – Dec of 2024.

Here, we can see that the salaries have increased by 3% which is the annual raise percentage. By switching from 1 month to 12 we get:

These numbers represent the calendar year (Jan – Dec) after the reference period.

Revelwood is an award-winning, Platinum Workday Adaptive Planning partner. We build solutions for the Office of Finance that minimize your risk by seamlessly incorporating business analytics into your everyday thinking. We help businesses achieve their full potential with Workday Adaptive Planning by combining the software with our best practices and expert guidance.

Limit the Drill Down List on a Workday Adaptive Planning Report

October 25, 2023 by Michelle Song

Our latest Workday Adaptive Planning Tips & Tricks video features Michelle Song, a senior consultant at Revelwood, demonstrating how easy it is to limit the drill down list on a Workday Adaptive Planning report. This new feature will transform how you – and your end users – interact with your Adaptive Planning reports.

Watch this video to learn how to:

Access the report
Edit the report properties
Select the relevant dimensions
Apply your changes
Save and Run

Now, when you click on an account name, you’ll be greeted with only the dimensions you’ve deemed pertinent. No more hunting for the right information or risking confusion for end-users!

By fine-tuning your reports, you’re not just saving time but also enhancing the overall user experience. No more distractions, no more unnecessary options—just precise, actionable insights.

Revelwood is an award-winning, Platinum Workday Adaptive Planning partner. We build solutions for the Office of Finance that minimize your risk by seamlessly incorporating business analytics into your everyday thinking. Combining the software with our best practices and out-of-the-box applications, we help businesses achieve their full potential with Workday Adaptive Planning.

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

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!

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, 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: Approaches in Planning Analytics’ New Set Editor

January 17, 2023 by Revelwood

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)

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

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

December 20, 2022 by Revelwood

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.

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

IBM Planning Analytics Tips & Tricks: Excel TEXTBEFORE & TEXTAFTER

December 13, 2022 by Lee Lazarow

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.

FP&A Done Right: Approaches to Long-Term Planning

November 14, 2022 by Lee Lazarow

“If you fail to plan, you are planning to fail.” – Ben Franklin

In our collective rush to react to ever-changing marketplace dynamics and shifts in the economy, it’s easy to focus on short-term plans, to the neglect of long-term planning. Today’s leaders need to have several plans – short-term, medium-term, and long-term.

Different plans for different needs

How do these plans differ? A short-term plan is designed to show granular details for a limited time frame. This is often updated monthly, although we have some clients updating their plans on a weekly basis. One of our clients follows a process where local managers update their plans on Mondays and Tuesdays, have the regional managers review the data on Thursdays, and allow senior management to analyze and assess the data on Fridays. Each Monday they start the process over.

Most organizations utilize a medium-term plan that looks out anywhere from a few quarters to a full year. Most people will think of this as a standard monthly forecast with data at a bit more of a higher level, but still somewhat details.

A long-term plan often goes out multiple years. Many companies create a 5-year plan, although some industries such as entertainment and pharmaceutical often create 20-25 year plans. A long-term plan is a high-level view of the business. It’s not nearly as granular as short, or even medium-term plans. The plan does not get down to the level of looking at a GL account or a customer. It’s a measuring tool and a defined way of reviewing the progress of the company. In short, long-term planning helps to set the company’s direction.

The essentials of long-term planning

The long-term plan gives you guidance on how to answer several questions, including:

How can we expand the company?
How can we look into acquisitions?
What products, geographies, and verticals can we or should we add?
What products no longer make sense?
How do debt payments impact cash flow?
What type of labor, buildings, locations, and equipment do we need?

A long-term plan can be considered a proactive approach to risk mitigation, enabling companies to plan, think ahead, prepare for, and lessen the impact of potential negative effects. At Revelwood, we recommend two approaches to long-term planning: the growth percent approach and a driver-based approach.

We often see both of these methods used when performing long-term planning in IBM Planning Analytics with Watson:

Growth percent approach

The growth percent approach allows you to adjust groups of data (accounts, departments, etc.) by increasing or decreasing the values from the previous year. Some clients prefer to simply use a single percentage (example: reduce all expenses by 2% each year for the next five years) whereas some clients prefer to include more variation (example: reduce utilities expenses by 2% next year, by 3% the following year, and by 4% for the next three years). But no matter what level of detail is used, Planning Analytics’ powerful scripting tool will perform the entire long term plan in a matter of seconds.

Driver-based approach

A driver-based approach uses operational activity to calculate key variable revenues and expenses. This approach allows you to simplify the input by defining a set of drivers and creating calculations that use the drivers. For example, a single driver of “units sold” can be used to immediately calculate revenue, COGS, and some of your variable expenses using the tool’s efficient calculation engine.

Mitigate risk with long-term planning

Long-term planning is your company’s assurance against planning to fail. There’s a reason why Franklin’s quote has lasted through the years. And it should be the motto of every planning team.

Learn more about long-term planning by watching our on-demand webinar – Long-Term Planning in IBM Planning Analytics.

This post originally appeared on IBM’s Journey to AI blog.

IBM Planning Analytics Tips & Tricks: MDX Syntax Explained

October 18, 2022 by Lee Lazarow

MDX enables you to query dimensions and cubes, lists of elements, data points, calculations, and more.

The first step in learning MDX is to understand the syntax. Below is an example of an MDX expression that returns all elements of the product dimension sorted in ascending order. To understand the expression, we’ve color-coded the syntax below.

Part1, the part that is associated with the TM1 structure and model, is in green. This defines cubes, dimensions, hierarchies, elements and more.

Part 2, in blue, is the MDX Function.

Part 3 is in red. These are known as curly brackets or braces. These convert the results of the MDX Expression into a set.

The Expression below lets you list all elements of the product dimension and sort the list in ascending order:

Revelwood recently hosted a webinar on How to Use MDX in your IBM Planning Analytics Environment. Watch the recording to learn more about using MDX.

Interested in learning more about MDX? We have another MDX-specific webinar on Wednesday, November 9, 2022 at 1pm ET. Attendees will learn advanced MDX functions and see real-life examples of how Revelwood used MDX to address complex reporting requirements and to solve client challenges.

Have a question about MDX? Register for our upcoming webinar or reach out to us at info@revelwood.com

Workday Adaptive Planning Tips & Tricks: Using Display Fields in Modeled Sheets

Read more Workday Adaptive Planning Tips & Tricks:

Limit the Drill Down List on a Workday Adaptive Planning Report

Read more Workday Adaptive Planning 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: Approaches in Planning Analytics’ New Set Editor

Watch more IBM Planning Analytics Tips & Tricks:

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

1. Shortcuts in PAW Models

2. Color in Planning Analytics Workspace Charts

3. Filtering in Planning Analytics Editor

Watch more IBM Planning Analytics Tips & Tricks Videos:

IBM Planning Analytics Tips & Tricks: Excel TEXTBEFORE & TEXTAFTER

Read more IBM Planning Analytics Tips & Tricks: