Data Spreading is the automatic distribution of a numeric value from a parent to leaf level cells beneath it. When a value is entered at a parent intersection, that value is distributed to all input cells consolidating up to the cell where the data was entered. For example, entering 1200 to a consolidation of all months will break it back to 100 per month, assuming there is no data already in place.
Example of Simple Data Spreading
A common method of data spreading, which is sometimes known as breakneck, is the equal or proportional distribution of a numeric value across all N level elements (leaves) leading up to the consolidation(s) at the entry cell. In more detail, a value of 1000 entered in a General Ledger cube, at an intersection made up of the following:
- Time dimension: 2024 consolidation element,
- Version dimension: Actual leaf element,
- Currency Exchange dimension: Local leaf element,
- Branch dimension: Total Company consolidation element,
- Account dimension: Balance Sheet (BSHT) consolidation element,
- Measure dimension: Value leaf element.
The number of descendants of each consolidation element at this intersection determines the distribution of the 1000 value.
NOTE: All leaf level cells for this intersection were initially zeros, therefore an equal data spread was applied. If there were existing values in these cells, a proportional data spread will be applied instead.
From the image above, we see the distribution of this 1000 value across all leaf level elements under Total Company and 2024, using the default method of equal data spread.
Asides from this default spread method, there are other data spreading methods available when you right-click a cell or a range of cells. In addition to the data spread methods, there are general procedures to follow when initiating a data spread.
How to Apply Data Spreading
Using Data Spreading in Planning Analytics is very easy. Essentially, you just select a cell, right click and away you go. Here are the steps in detail:
- Select a cell or a range of cells where data spreading is to be applied to. It’s good to note that some data spread method will not be available to use (i.e. the option would be greyed out) when a single cell or a range of cells are selected.
- Choose the data spreading method to apply.
- Set the value to be distributed.
- Set the update action to be applied for data spread. There are three update actions to choose from.
- Replace: it replaces any existing value.
- Add: it increases existing values by the distributed values.
- Subtract: this reduces existing values by the distributed values.
- Set the direction in which distribution of values are to extend to. The direction options are; Left, Right, Up, and Down.
Data Spreading Methods
There are a large number of data spreading methods. We will detail each below. Here is the list and links down to the relevant section:
- Proportional – proportionally change existing values to a new total
- Equal – modify all children by an equal value
- Repeat -repeats value across a range of cells
- Clear – sets all children values as zero
- Percent Change – increase values by a certain percentage
- Repeat Leaves – repeats value across leaf level cells
- Equal Leaves – equal distribution of values to all children with an assumption of positive element weights
- Straight Line – linearly distributes data using defined start and end value
- Growth Percent – sequential increase of values by a growth percent
- Relative Proportional – proportionally updates all children using a reference cell
- Relative Percent Adjustment – proportionally adjusts reference cell values according to a defined percentage
1. Proportional
This method proportionally spreads a value amongst all cells that are children of the value entry cell based on the proportion of existing values in these cells to the entry cell. For example, in image 2 below, we have existing values which lead to a total value of 3,650 for Sydney (101) branch in 2024. However, a value of 7,300 is to replace the existing total value, and be proportionally spread across the range of cells below.
To proportionally spread this 7,300 value at the cell intersection of Sydney (101) and 2024, a data spread is initiated using a calculation similar to the one shown below:
- OBa-23 has a value of 100, so (100/3650) * 7300 = 200
- Jul-23 has a value of 200, so (200/3650) * 7300 = 400
- Aug-23 has a value of 250, so (250/3650) * 7300 = 500
- Sep-23 has a value of 300, so (300/3650) * 7300 = 600
- Oct-23 has a value of 350, so (350/3650) * 7300 = 700
- Nov-23 has a value of 400, so (400/3650) * 7300 = 800
- Dec-23 has a value of 450, so (450/3650) * 7300 = 900, etc..
To do this there are two methods:
- The default break back method is proportional spread. With this, you can directly type over the top of the existing cell and TM1 will break the new number back across all the child elements in direct proportion to the old data.
- The second method is the standard way of doing spreading – using right click. To use it, right-click the cell at 2024 and Sydney (101) intersection. Then choose the “Proportional” method, set the value as 7300 and set the update action to “Replace”. The image below shows the complete proportionally spread value of 7300.
A shortcut for proportional spreading with a replace update action is to prefix a “P” (case-insensitive) before the value and enter this directly in the cell. E.g.: p7300.
2. Equal
This method allows for equal distribution of a value across a range of cells in a cube view. Say for example, we want to add a value of 5 to the values in image 3 above.
First, select all thirteen cells leading up to the intersection of 2024 and Sydney (101) and right-click the selection. Then choose the “Equal” method, set update action as “Add”, and enter the value as 65. This method adds a value of 5 to all thirteen cells. See image 5 below.
3. Repeat
This repeats a value within a range of cells in a cube view. Let’s say a value of 50 is to be repeated in the cells below the intersection of 2024 and Sydney (101), in image 5 above. When you right-click the cell intersection, choose the “Repeat” method and set the value as 50. Then set update action as “Replace” and choose “Down” as the direction.
Image 6 below shows all thirteen cells replaced with a value of 50.
4. Clear
This clears existing value in a cell or a range of cells. For example, using this method at the intersection of 2024 and Sydney (101) would clear out all values in cells leading up to that intersection. A shortcut for this method is entering a “C” at the cell intersection. See images 7 and 8 below.
5. Percent Change
This method increases a range of cells by the indicated percentage change. Take for example the existing view in image 2 above. To increase those values in the view by a 100%, right-click the cell intersection of 2024 and Sydney (101) which was 3600 in image 2 above. Choose “Percent change” method, set the percentage change value as 100, and set the update action as “Add”. This doubles the existing values. See image 9.
6. Repeat Leaves
Unlike the Repeat method which repeats a value across a range of cells in a cube view, the repeat leaves method repeats a value across all the N level elements (leaves) that child to the cell intersection where the data spread was defined. For example, at the intersection of 2024 and Sydney (101), a value of 50 is to repeated in cells of leaf level elements. This will place 50 into all leaf level cells underneath the intersection.
To do this, right-click the intersection and select “Repeat leaves” method and set the value as 50. Set update action as “Replace” and apply update to “All leaf cells”.
You can choose which cells to update: Populated Leaf Cells and All Leaf Cells. The “Populated leaf cells” option updates only cells with existing values that are not zero, while “All leaf cells” updates all cells, regardless of what their contents are.
Image 10 below shows how the value of 50 adds up to the consolidated elements in the in the cube view and Image 11 shows how the value of 50 has been applied to all children.
To view how the values at the leaf level, we’ll add the account dimension to the column section and drill on of the consolidations down to the leaves. See image 11. Under columns Cash (1000), Debtors (1200) and Inventory (1300) we can see the 50 value in there.
7. Equal Leaves
This method equally distributes a value across all leaf level cells that are child of to the input cell. It is however important to note that this method initiates the distribution of a value as though element weights are positive. Visit this post to know more about element weights in TM1.
To demonstrate this, we added account dimension to the column section with the consolidated element of Net Assets (NEAS) expanded to its descendants. We then enter a value of 1000 directly at the intersection of 2024, Sydney (101) and Net Assets (NEAS). This initiates a default equal data spread.
See image 12 below to see how this value was distributed to the leaf level cells.
In image 13 above, take note of the weightings applied to Total Assets (TTAS) and Total Liabilities (TTLB) – direct children of Net Assets (NEAS). Also, take note of how leaf level values of these direct children add up to ensure both the intersection of TTAS and TTLB at 2024 result to a value of 1000 at Net Assets (NEAS), as shown in image 12. This is an even data spread to leaf level cells which takes into consideration the element weightings.
On the other hand, to apply equal leaves data spread, right-click the intersection of 2024, Sydney and Net Assets (NEAS). Choose “Equal leaves” method and set update action as “Replace”. Then apply spread to “All leaf cells”. The result as seen in image 14 shows how the equal leaves distribution to all leaf level cells add up to the intersection of TTAS and TTLB at 2024, and how the values at this intersection manually adds up to 1000. However, the value at the intersection of 2024 and Net Assets (NEAS) is 250, which is subtraction of its direct children cell values, as per the defined element weights.
8. Straight Line
This method linearly spreads data across a range of selected cells by defining the start and end values for that range. For example, say we want to distribute data across thirteen cells beginning and ending with values 100 and 1300 respectively, at the intersection of 2024 and Sydney (101). To do this, select those thirteen cells and right-click. Set the method to “Straight line”, set start value as 100 and end value as 1300, then set update action as “Replace”. This populates the range of cells with values of equal increments from 100 to 1300. See image 15 below.
9. Growth Percent
This method populates a range of cells with sequential increments from the defined starting value, using the defined growth percentage used for calculating the increments. An example for this is populating the cell at the intersection of OBa-23 and Sydney (101) with a value of 100, and defining the values for the twelve other cells after it to be an increment of the prior cell value by 10% (see image 16 below).
To do this, select the thirteen cells (OBa-23 included) and right-click. Then choose “Growth percent” method and set the value as 100 and the percentage change as 10.
10. Relative Proportional
Similar to the proportional method which uses proportions to determine data spread, this method uses a proportion of all leaf values leading to a cell intersection as a reference for the proportional percentage to use for the distribution of a value. Lets take for example the distribution of 18,200 value across leaf level cells making up the intersection of 2024 and Brisbane (102), using the proportion of leaf level cell values leading up to the intersection of 2024 and Sydney (101), as shown in image 15 above.
To do this, right-click the cell at 2024 and Brisbane (102) intersection and choose “Relative proportional” method. Then set the reference cell at intersection 2024 and Sydney (101), and set the distribution value as 18200. See the distributed value in image 17 below.
11. Relative Percent Adjustment
The difference between this method and Relative Proportional method is the definition of a percentage adjustment value rather than a distribution value.
This method populates the target leaf level cells with percentage adjusted values from the reference leaf level cells. Let’s say we want to replicate the values shown under the intersection of 2024 and Sydney (101) in image 17 above, but with a 150% (1.5 times) adjustment to the values. These adjusted values will be populated across the leaf level cells leading up to the intersection of 2024 and Brisbane (102).
Therefore, to do this, we would right-click the intersection and choose “Relative percent adjustment” method. Then set the reference cell as 2024 and Sydney (101) and set the percentage adjustment as 150. Image 18 below shows the distribution of the value.
Need Help with Data Spreading?
Using spreading is a great way to setup a plan or forecast. Do you want to know more on how to apply data spreading to your business? Fill the form below and ask away! We can’t wait to hear from you, and helping your business continuously thrive!
