Often overlooked and underused, TM1’s Data Spreading Options provide spectacularly advanced features and simple solutions to everyday planning problems.
I’ll quickly cover some of the most common uses of spreading functions, but before I do it should be noted that Cube Rules and Data Spreading do not play nice. Rules and even Conditional Rules which evaluate to STET can prevent the ability to use Data Spreading Features.
Spreading By-Product – Copying data between Scenarios
Relative Proportional Spreading can assist in many traditionally time-consuming tasks such as breaking a SKU level Sales Budget back by customer using the proportions from the prior years Actual.
A by-product of the Relative Proportional Spread function is the ability to clone data from element to element. This has the obvious advantage overwriting a Turbo Integrator process or Rules as the spread is all done within a front-end and has no scripting component at all.
Clearing our Whole Portions of the cube
Unlike Relative Proportional Spread, the Clear spread function is clearly intended for a single purpose. Selecting a high level position on the cube and using this function will remove all data from that point down through all descendant elements.
Note: Rollup Elements and Data Spreading – If you need to clear data or copy data from an element to another in one dimension but have another dimension in the same cube which has elements which are not aggregated into a single parent you can make a temporary aggregation using the Custom Rollup feature (found in the subset editor) and then apply your spreading functions against that.
Data Spreading Shortcut Codes
There are shortcut codes which can be used in the TM1 Cube Browser, TM1 Web, Contributor and Excel Reports which can save time and monotonous keystrokes.
General Syntax:
{Function Code} {Direction (often optional)} {Action Modifier (optional)} {Value/Value Range}
For Example: “R>500” which will repeat the value “500” from the selected cell and to the right (because of the “>”).
Direction Options:
- < Left
- > Right
- ^ Up
- | Down
Data Modifier Options:
- + Add to the existing value
- ~ Subtract from the existing value
- *Omitted Overwrites the existing value
Spread Functions:
Name | Code | Examples | Description |
Repeat | R | R>50 R<>0.1 (10%) R>+100 |
Repeats a value in the given direction using the optional data modifier argument |
Equal Spread or Proportional Spread |
S
P |
S1000
P250000 |
Proportionally Spreads the given value across populated descendant cells.
(I haven’t found a difference between how these two work? – Please comment if you know.) |
Percent Change | P% | P%101 | Proportionally Spreads the given percentage across populated descendant cells.
Note: for this function 100 is 100% – this is unlike percentages stored as data in TM1 with which 1 is 100%. |
Straight Line | SL | SL>10:100 | This applies an even growth from the first specified number to the second across items in the direction specified. |
Growth % | GR | GR>1000:10 | This applies a given (second number) growth on the specified base number across items in the direction specified.
Example Explained: Starting with 1000 in the first cell apply 10% increase on the prior value for each cell to the right. |
Clear Values | C | C^|<> | Removes all data from the cell selected an in the direction specified.
Example Explained: Clear the selected cell and ever cell above, below, to the left and right in straight lines. (Think of how the rook moves in chess) |
Additional Spread Functions:
- Equal Spread Leaves – This will calculate the exact number to be used across all descendant cells equally to result in the specified amount of change at the spread level.
- Repeat Leaves – This sends the given value into each currently populated descendant cell (using the data modifier selected)
Data Holds and Spreading
This is no small feature and is something we will look into in more detail in a later post.
A full reference of Spreading functions can be found on IBM’s Documentation Portal here.
Note: If you know the difference between Equal Spread and Proportional Spread please leave a comment below as using them in the Cube Viewer seems to result in a basic Proportional Spread for each function.