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:

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:

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:

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:

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.

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