Using Conditional Formatting & Calculations with IBM Planning Analytics Workspace

By: James Miller

January 13, 2021

IBM Planning Analytics Workspace (PAW) is a web-based interface for IBM Planning Analytics. It delivers an interface to the data in planning analytics (TM1), providing ways to plan, create, and analyze your content by providing features such as conditional formatting and calculations.

Conditional formatting is a feature in many spreadsheet applications that allows you to apply specific formatting to cells that meet certain criteria. It is most often used as color-based formatting to highlight, emphasize, or differentiate among data and information stored in a spreadsheet. Using conditional formatting in PAW, you can highlight specific values or cells, by changing what a cell looks like based on a set of conditions that you set.

Calculations

Also, in PAW, you can add “calculations” (think of them as being similar to Excel formulas) to a view to enhance your analysis. These calculations are saved as part of a view in the Planning Analytics Workspace “content store”. You can create two types of calculations: “Summary” calculations that apply to all visible leaf members and unexpanded consolidated members on a row or column and “Member” calculations that apply to selected members on a row or column.

For example, Rank is an example of a “handy” PAW member calculation. The Rank calculation automatically ranks the values of the selected member. Rank 1 is the highest value. Rank is applied to the values visible in the view. If you drill down or roll up consolidations in the view, or if you modify the set used in the view, rankings change to reflect the ordering of the values that are visible in the view.

An Interesting Exercise

Armed with the above information, suppose that you have annual sales data in a Planning Analytics cube measured by store location. With that we have created a simple view showing total sales for 2018 for each store location:

To make the above view a bit more interesting (and functional) we can add a Rank (member) calculation. If we want to rank the store locations by their 2018 Sales totals, we can select and right-click the column header labeled “2018” and then select Create calculation…:

On the “Create (Calculation)” dialog, under “look across the column values”, select the Rank option. Note that we could also change the name for our calculation, but “Rank(2018)” fits, so click OK:

Now we can see that a new column “Rank(2018)” has been added to our view showing the calculated store locations “rank” based upon the store’s annual sales total:

Nice! But notice that the view displays the store locations in alphabetic order and what we might expect to see is the store locations ranked by their annual sales. To achieve that, just right-click on the Rank(2018) column heading and select Sort ascending:

Voila! Now we have a nice, ordered “store by sales” scorecard view:

Conditional Formatting

Now, let us add some color to our “scorecard view”. Start by right-clicking on the “2018” column header and the select Conditional format….

You will find that the Conditional format dialog is someone similar to the “Conditional Formatting Rules Manager” in MS Excel. It lists each formatting rule in the order that it will be applied and for each rule, you will see the selected “member” (to apply the rule to), an formatting rule “operator” (like greater or less than or “is empty”), a member/value selector which indicates if the rule is invoked by a “member” or “value” found in a cell, a comparison field (either a specific member or a value), a preview field (which gives you a visual of how the cell will look with the formatting rule applied) and an edit icon (used to open the conditional formatting styling dialog). On the far left of the dialog are icons allowing you to Add a rule, Remove a rule as well as Reorder the rules.

For our scorecard view, the member in the header that we selected is displayed (2018). We could change this selection by clicking the member name and selecting a new member, but we will leave 2018. Next, we can set the operator to less than, change the member/value to “value” and enter the numeric value 9999999.   With that rule set, we can then click the add icon and add two more conditional formatting rules, so that each rule will apply to a range of values in the sales column as shown:

Finally, for each rule, you can click the edit icon to access the Conditional Format Styling dialog where you can click and select what you want the cell to look like: cell Fill Color and Border Color, Font Color or Style, and Icons (which can add an additional visual cue to a cell). A preview of the selected formatting is also shown to give you an idea of how the formatting will work.

Once you are satisfied with the formatting, you click Apply. Back on the Conditional format dialog, you can use the arrow keys to reorder the conditional format rules defined. The order of the rules determines which formatting is used if there are conflicts between the rules. For example, we defined three formatting rules on sales value; and two of the rule’s “overlap”. “>= 9999999” AND “> 19999999”. Additionally, each rule has a different color background defined for the column defined. In this case the order defines which rule applies. Once you are “ready”, you can click Save and your see the conditional formatting rules applied to the view:

If you are happy with the results, don’t forget to Save the updates to your view so that they will be applied to each time the view is opened in PAW (from the view toolbar, select and click on Save view as and enter a name for the view, perhaps “Stores by Sales”).

Release Information

IBM Planning Analytics Workspace 2.0.56

Ask QueBIT

Want some help with using calculations and conditional formatting to improve your Planning Analytics Workspace experience? Have a specific question? You can always reach out to QueBIT at support@quebit.com for assistance. We’re here to help!

QueBIT’s Analytics Online Instructor-led Training Courses

  • IBM Planning Analytics Developer
  • IBM Planning Analytics Advanced Reporting
  • IBM Planning Analytics Advanced Developer

Visit learn.quebit.com

QueBIT’s Analytics Online Instructor-led Training

  • IBM Cognos Analytics Authoring Reports Fundamentals
  • IBM Cognos Analytics Authoring Reports with Multidimensional Data
  • IBM Cognos Analytics Authoring Reports Fundamentals

Visit learn.quebit.com

Subscribe to QueBIT Notifications

Can’t find what you’re looking for? →

    Contact Help Desk

    This form collects your contact information so that we can correspond with you. For more information on how we are committed to protecting and respecting your privacy, please review our Privacy Policy.

    I consent to QueBIT collecting my contact information.