What do we typically mean when we say, “data spread”?

Well, perhaps the simplest way to understand this concept is by thinking of it as the act of “applying data across a range” – say a period of time. Perhaps you expect expenses to total a certain amount for the year, but you need to break that value out as individual monthly expenses. In that case, the total amount would be “spread” across each of the 12 months.

IBM Planning Analytics Workspace (“PAW”) provides a variety of pre-defined ways that can be used to distribute numeric data to cells in a cube.

Spreading Methods

As in TM1 Architect and PAfE, there are a number of spreading methods that you can use in Planning Analytics Workspace and one easy way to invoke them is by using a key or a combination of keys known as keyboard shortcuts.

To use these spreading (keyboard) shortcuts, you need to determine the direction of the intended spread, the action of the spread and the spread method.

Spread Direction

You can specify the direction for data to be spread (Up or Down, Left or Right) by using the following keyboard codes:

  • Up: ^
  • Down: |
  • Left: <
  • Right: >

The direction is across a grid of values, relative to the cell you are starting the spread from. Examples are provided below.

Spread Action

By default, the spreading action is to replace existing data. That means that the numeric value being spread will overwrite (replace) whatever data is there. You can also specify for the spread value to be subtracted or added to the existing or current value. To specify that the spread value be subtracted, you use a tilde (~); using a plus sign (+) will cause the spread data to be added.

Spreading Methods

The following list summarizes the spreading methods available for you to use:

  • Proportional – “P” Proportionally spreads a value to all leaf cells on the row of insertion and replaces the existing cell values.
  • Equal spread – “S” Equally spreads a value to all leaf cells on the column of insertion and adds the product of spreading to the existing cell values.
  • Repeat – “R” Subtracts a value from all leaf cells to the left of the insertion point.
  • Clear – “C” Clears values from all leaf cells in the view.
  • Percent change – “P%” Applies a percent change to all leaf values, adds the product to the existing cell values, and increments all leaves.
  • Repeat leaves – “LR” Adds the spread value to all leaves of the consolidation. If you omit *, it copies the spread value only to populated leaf cells. Note that performing a repeat leaves spread affects leaves in the consolidation below the cell you “spread from”.
  • Equal leaves – “LS” Distributes the spread value equally across populated leaves of a consolidation.
  • Straight line – “SL” Replaces all leaf values to the right of the point of insertion, with a start value and an end value Can be used across a single row or column, but not across rectangular ranges.
  • Growth percentage – “GR” Applies a growth percentage to the starting value and replaces all leaf values below the point of insertion.

Examples

A popular example of using keyboard shortcuts to spread numeric data is the following.

A time dimension appears on columns as Year followed by 12 months. On rows, an account is populated for all of the 12 months and is showing different values for each. You want to update the account to show 24,000 units being sold equally divided across the months.

You could make 12 entries – entering 2,000 into each of the 12 months or you could just enter S>24000 into the first month (S is the shortcut for Equal Spread):

 

 

 

 

 

Result:

 

 

 

 

 

How about a Proportional spread example?

What if you want to proportionally spread a total value of 5,000 across the same 12 months? To do that, you can use the shortcut: P<>5000. This would proportionally spread the value to all cells on the row of insertion (each month), and replaces the existing cell values:

 

 

 

 

 

Result:

 

 

 

 

 

Ask QueBIT

Want to discuss spreading or keyboard shortcuts within the PAW interface? You can always reach out to QueBIT at support@quebit.com for assistance. We’re here to help!