IBM Cognos TM1

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

June 23, 2020 by Lee Lazarow Leave a Comment

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?

The FILTER function allows you to filter a range of data based on criteria you define. This allows you to create selector cells which can be used within a formula to filter the selected data. The function is written as follows:

FILTER (range, formula for inclusion, if empty)

The range consists of the entire table to be checked
The formula for inclusion consists of criteria to be filtered
The last parameter (optional) defines a value to be returned when no results are found

The example below shows a table on the left, an input cell, and the FILTER command in cell F5. In this case, the function is being used to look at the entire table (cells B3 through D11) and checking to see if the value in column B is the same as the value in cell G2.

The formula for inclusion can use more complex approaches to check multiple values. This is done by using the multiplication operator as the “and” statement. The example below expands upon the first example by allowing the user to independently select both a state and a city.

IBM Planning Analytics Tips: Excel's FILTER Function

This functionality allows you to replicate Planning Analytics selector concepts into an existing spreadsheet.

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: The Excel FORMULATEXT Function

June 16, 2020 by Lee Lazarow Leave a Comment

Have you ever looked at a value on a report and wanted to know the formula that was used, even though you may not have access to the report? Have you ever wanted to easily include a formula as part of the report? Have you ever wanted to show people how complicated your calculation was as an attempt to get them to better appreciate you?

Well, all of this can be done in Excel with the use of a simple formula: FORMULATEXT.

The FORMULATEXT function is used to return a formula as a string. The single parameter of the function is the cell to be converted to text.

The following example shows the use of the formula in cell C6.

IBM Planning Analytics Tips & Tricks: The Excel FORMULATEXT Function

Not only can you now show your co-workers how complicated your nested-if formulas have become, but you can also end those recurring nightmares about your middle school math teachers who always said “show me your work” (ok, maybe that’s just me).

Technical Bulletin: Security Change Impacts MDX

June 11, 2020 by Lee Lazarow Leave a Comment

IBM has decided to revert the change in behavior that is described in this tech bulletin. The change will be made in version 2.0.9.2 and it is expected to be released by the end of July. For more information, please reference IBM’s tech note at https://www.ibm.com/support/pages/node/6226890

We have an important technical bulletin regarding IBM Planning Analytics and MDX.

Version 2.0.9.1 of Planning Analytics changes the way MDX expressions are generated for dimensions that have security defined. This change may have a significant impact on your existing models.

Details

Prior to 2.0.9.1, when generating a subset list based on an MDX expression, Planning Analytics would execute the dynamic subset and then introduce security. Elements in the subset would be determined at runtime even if the user using the subset did not have READ access to a member that is named in the MDX statement. The list of elements in the subset was determined and then filtered by security. This resulted in the user only seeing the elements that were allowed.

As of 2.0.9.1, the system introduces security and then executes the dynamic subset. Users will need to have (at least) READ access to members that are named in the MDX statement. Just as a user could not determine the descendants of a consolidated element in any other user interface (e.g., the hierarchy or set editor) they will not be able to determine descendants of the member by using MDX. This may result in users not seeing any elements in the resulting expression.

For example:

A model contains a dimension named “Company”. The dimension has a consolidated element named “Total Company” with child elements “A”, “B”, and “C”.
There is a dynamic subset which returns the base level descendants of “Total Company” via the following expression:

{ TM1FILTERBYLEVEL ( { TM1DRILLDOWNMEMBER( { [Company].[Total Company] }, ALL, RECURSIVE ) } , 0)}

A user only has security access to company A via element security.
- Prior to 2.0.9.1, the result would be that the user sees company A.
  - This is because Planning Analytics created the dynamic subset which included all the elements and then applied security to only allow the user to see company A.
- As of 2.0.9.1 the result does not include any elements in the subset.
  - This is because Planning Analytics tried to create the subset by starting with Total Company, realized that the user has no access to Total Company, and therefore did not have anything to expand.

Recommended next steps:

Revelwood recommends a review of your system to determine where MDX expressions are used. This includes all dynamic subsets and all dynamic reports/templates (i.e., Active Forms / Dynamic Reports).

Revelwood has created a Turbo Integrator script that will help you assess potential conflicts by analyzing combinations of element security and dynamic subsets. You can download the script for free.

Reviewing your system now will reduce the potential for your end users having problems seeing elements. By being proactive, you have the opportunity to stave off headaches for yourself.

Please contact John Pra Sisto at jprasisto@revelwood.com if you have any questions regarding this bulletin or the Turbo Integrator script.

IBM Planning Analytics Tips & Tricks: The Excel DATE Function

June 9, 2020 by Lee Lazarow Leave a Comment

Many of you already know about Excel’s DATE function. The function is used to create a date by defining specific values for the year, month and day.

DATE(2020,7,20) gives a result of 7/20/2020.

And some of you know that you can also use formulas to calculate a date.

If a value of 5 exists in cell C4 then

DATE(2020,7,20+C4) gives a result of 7/25/2020.

But did you know that the formula will also check for valid dates and, if needed, shift the month automatically?

DATE(2020,7,35) gives a result of 8/4/2020

(since there are not 35 days in July!)

and

DATE(2020,7,30+C4) gives the same result

Not only can this formula be used to calculate variable dates, but it will also ensure that you don’t incorrectly calculate a value that doesn’t exist on the calendar. After all, we wouldn’t want Julius Caesar coming back to question our use of “his” month!

IBM Planning Analytics Tips & Tricks: Using Words and Numbers Together

June 2, 2020 by Lee Lazarow Leave a Comment

In an IBM Planning Analytics Tips & Tricks video, I demonstrated the concept of using words to manipulate your views. This approach also applies to entering data. All of you know that you can enter data by typing a number into a writeable cell.

IBM Planning Analytics Tips & Tricks: Using Words and Numbers

Some of you know that you can use commands such as “2k” to enter a value that will be converted into a number.

IBM Planning Analytics Tips: Using Words and Numbers

And some of you also know that you can type commands such as “grow10” on an existing number to populate the rest of the year.

IBM Planning Analytics Tricks: Using Words and Numbers

But did you know that you can merge all of this together into a single entry? For example, if I type “5kgrow20” in January then 5,000 will be input into January and a 20 percent growth rate will apply to each of the other months.

Using words and numbers in IBM Planning Analytics

The combination of numbers and phrases in a single cell will help you quickly and easily create plans for multiple time periods.

IBM Planning Analytics Tips & Tricks: The Data Load Process

May 26, 2020 by Lee Lazarow Leave a Comment

In an IBM Planning Analytics Tips & Tricks video, I showed how to use a drag and drop approach to load data into a cube. Since that time, the wizard has been updated to allow you to save your import as a new TurboIntegrator process and to also schedule the process within a chore.

After dragging the file onto the cube, the first screen that appears is a confirmation of the file with some file-based setup aspects such as the delimiter, the quote character and the number of header rows:

IBM Planning Analytics Tips & Tricks: The Data Load Process

After clicking the continue button, the second screen appears which allows you to define the columns and aspects associated with the data load (before and during):

The Data Load Process in IBM Planning Analytics

The bottom, right corner of this screen allows you to save the import as a process and/or a chore:

IBM Planning Analytics Tips: The Data Load Process

Once executed, the process will be saved and can be referenced using PAW’s process functionality:

IBM Planning Analytics Tricks: The Data Load Process

This approach within Planning Analytics allows you to use a drag and drop approach to create and save Planning Analytics code.

IBM Planning Analytics Tips & Tricks: The CellValueN Formula

May 19, 2020 by Lee Lazarow Leave a Comment

Rules in IBM Planning Analytics allow you to reference components of a cube as part of your formula … either from the cube you are currently in or from another cube. This is done via the use of the DB function. The syntax of the DB function is:

DB(cube, elem1, elem2, …)

Where each elem value references an element from the dimensions of the cube, in the order of the cube definition.

This is a great formula if you know all the dimensions of your cube in the proper order. But what if you don’t want to have to define every dimension in your cube?

Planning Analytics also allows you to use a formula called CellValueN. The syntax of the CellValueN function is the same as the other formula:

CELLVALUEN (cube, elem1, elem2, …)

… but there are some differences when using this formula.

You do not have to reference every dimension! Any undefined dimensions are the equivalent of using the exclamation point, often called a “bang.” This means that you only need to reference dimensions/elements that are different from the source value.
Since you are not putting your dimensions in order, you must reference the dimension name in front of each element. The dimension name must be written within a set of single quotes, a colon is then used without quotes, and the element is then written within another set of single quotes.

For example, account 605100 would be written as ‘Account’:‘605100’

There are different versions of the rule. One version is CellValueN which pulls numeric values and one version is CellValueS which pulls string values.

Here is an example of the rule. Note that the cube contains seven dimensions but the formula only references two of them.

IBM Planning Analytics Tips & Tricks: CellValueN Formula

This approach allows you to simplify your rules by referencing a data point without having to reference every dimension within the cube.

IBM Planning Analytics Tips & Tricks: Max Number of ELSEIF

May 12, 2020 by Lee Lazarow Leave a Comment

Most of you know the concept of an IF statement within TurboIntegrator

IF (expression);
statement1;
ENDIF;

… and most of you also know the concept of nested-IF statements

IF (expression);
statement1;
ELSEIF (expression);
statement2;
ENDIF;

… and most of you also know the concept of generically using an ELSE command as a catch-all

IF (expression);
    statement1;
ELSEIF (expression);
    statement2;
ELSE;
    statement3;
ENDIF;

But did you know that Planning Analytics has a maximum number of nests that can be included within a TurboIntegrator process? You can nest up to 20 ELSEIF/ELSE statements in a process. If you exceed 20 nested statements then you will receive an error when attempting to save the process.

If you come across situations where you are using a lost of nested IF statements, then there may be a way to re-assess the expressions that are being used. Give us a call!

Tech Bulletin: New IBM Planning Analytics Features for 2020

May 11, 2020 by Lee Lazarow Leave a Comment

IBM introduces new features for Planning Analytics in releases throughout the year. We publish IBM Planning Analytics Tips & Tricks on these features every Tuesday in our Knowledge Center. If you would like to receive these in your inbox every Tuesday, you can subscribe here.

Here we list out which useful features have been released so far this year for PAx and PAW.

New PAx Features in 2020

2.0.50 (Feb)
Design Mode can be used to enable or disable the refreshing of an Exploration View upon every change. As of 2.0.50, you can now also use Design Mode to preserve or clear DBRW formulas when you are copying and pasting values into cells.

2.0.51 (Mar)
As of version 2.0.51, you can now bulk update the data source or package for multiple Exploration Views at the same time.

2.0.52 (Apr)
Various improvements were made to the set editor to make it easier to work with large dimensions. These include the ability to focus on one area at a time, have easy access to various functions, perform drag and drop functionality, and define default settings for the available members and current set panes.

New PAW Features in 2020

2.0.48 (Jan)
Ability to abbreviate numbers to thousands or millions within the Set Format options

2.0.49 (Feb)

Changed hierarchy sorting to an alphabetical approach
Ability to enable security access to processes

2.0.50 – 2.0.52 (Apr)

Set editor improvements (see Pax 2.0.52 for details)
The AgentActions.log file can be used to review the user and time associated with these database actions: start, stop, restart, and end process
Modified various icons and the settings within the refresh menu

If you have any questions about these features, please feel free to reach out to me at llazarow@revelwood.com.

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

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: The Excel FORMULATEXT Function

Read more IBM Planning Analytics Tips & Tricks:

Technical Bulletin: Security Change Impacts MDX

Details

Recommended next steps:

IBM Planning Analytics Tips & Tricks: The Excel DATE Function

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Using Words and Numbers Together

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: The Data Load Process

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: The CellValueN Formula

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Max Number of ELSEIF

Read more IBM Planning Analytics Tips & Tricks:

Tech Bulletin: New IBM Planning Analytics Features for 2020

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

IBM Cognos TM1

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Details

Recommended next steps:

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:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT