Weights in TM1 – What They Are and How to Use Them
What are Weights in TM1?
Weights in TM1 are used to change the way an element in a dimension is added up into a parent element. For example, if you have Sales, Cost of Sales and Gross Profit as elements. Typically you would want to have these elements in a hierarchy, so TM1 will add them up. You also want it set up so you can drill down on Gross profit to get the children, Sales and COGS.
That works fine when you have a positive value for Sales and a negative values for COGS, so Gross Profit will be the net of the two (so, Sales 100, COGS -75, you add the two and get Gross Profit of 25). But what happens when you have two positive numbers for Sales and COGS. If you added 100 and 75 together, you’d get 175, which is clearly wrong.
The Impact of Weights in a TM1 Hierarchy
Building on from the above example, we need to turn around the sign of COGS so it is subtracted from Sales. If we were doing this mathematically, we would use the formula Gross Profit = Sales + COGS *-1. Well, weights do exactly the same thing. Every element is multiplied by it’s weight when it is consolidated.
Therefore, if we assigned the weight of 1 to Sales and -1 to COGS, then we can use the natural hierarchy of TM1 to do the consolidation. This is rather than writing a rule, or forcing negative values to be entered. And we get to drill up and down in the dimension using the hierarchy. Cool, huh!
What about Children of a Negative Number?
What about when you have multiple elements that add to a parent and the parent to a higher element again, which is then treated as a negative to yet another parent. Take for example the following balance sheet accounts. We have Assets and Liabilities and when you take liabilities from assets, in accounting speak, you get Net Assets.
Non Current Assets
Other Current Liabilities
Non Current Liabilities
If we were designing a dimension in TM1 to give us the drill down and to have Liabilities subtracted from assets, we would have all of these accounts have a weight of 1, except Liabilities. The reason we would not have a weight of 1 on Liabilities is that if we were creating a formula for net assets, it would be Net Assets = Assets + Liabilities *-1.
What about Weight on Child Elements?
Further, there is no need to apply a negative weight to any of the children of Liabilities . In fact, if we did, we would end up very odd results. Imagine if Leases, Loans and Non Current Liabilities all had a weight of -1. if we had 150 in Leases and 100 in Loans, then it would calculate -250 for Non Current Liabilities – obviously wrong.
Can an Element Have Multiple Weights?
Well, yes. The weight on an element is defined for the hierarchy it is being rolled up in. So, COGS all the way at the top of this post would have a weight of -1, so we calculate Gross Profit correctly. But what if you wanted to include it with another account and then add them up? Well, in that new hierarchy, you could set the weight of COGS at 1, so ti would be added to the other element to derive the subtotal.