Often overlooked and underused, TM1′s Data Spreading Options provide spectacularly advanced features and simple solutions to every day 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 over writing 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.
The difference between Equal Spread and Proportional Spread is that Proportional Spread will skip any zero values and adjust the rest of the values to get to the right total. So if you have ten fields, but only five of them have existing values, a proportional spread of 10 will result in a value of 2 in each field that already had a value. Whereas an equal spread of 10 would result in a value of 1 in every field.
Sound like thats what should happen, Give it a go with TM1 and see if thats what happens though. I’m getting the first example of values in both situations. Thanks for the comment!
I tested it before I replied. Go figure.
To add to the difference of proportional and even spreading, think of proportional spread as a profile spread. A profile spread takes into account the existing relationship of the values in the spread range and maintains that relationship as it applies the spread of the new value. An even spread ignores the existing profile and spreads the new value equally to the cells in the spread range.
Example:
Spread 150 to each month of Q1
Before Spreading
J F M Q1
Sales 30 40 30 100
Proportional Spread
J F M Q1
Sales 45 60 45 150
Even Spread
J F M Q1
Sales 50 50 50 150
Proportional maintains the profile of the range before the spread, where even spreading simply divides the value evenly based on the number of cells it is spreading over.
In the later versions of TM1 a proportional spread will still work on consolidations that have a value of 0 (i.e. no underlying values rolling up into that consolidation). It will simply perform an equal spread. This is a really handy feature!