TM1 Forum

Hi

I am struggling with Equal Spread allocation formula using TM1 rule. I

I know how to do it using Server Explorer/Active forms.

I want to spread my consolidated element value to all its children. Is there anyway to do this?

Thanks

JayM wrote:I want to spread my consolidated element value to all its children.

Can you elaborate what you mean by this? Where does the value sit? Consolidations in TM1 cannot store numeric values, so in order to "have" any values they have to have them calculated, either by consolidating, or by a rule-driven computation. Now, if you want your consolidated value which is (typically) a weighed sum of all its children copied onto all of its children which in turn roll up to the consolidation which is copied onto all its children which... Well, you get it. It has circular reference written all over it.

If you really mean that you have a value that you want to spread (but that isn't yet on your consolidation, but somewhere else) then you can either look at the TI function CellPutProportionalSpread (but it only affects leaves already containing non-zero values), or implement an algorithm in a TI. A rule should be possible as well, in fact, if you're willing to play a little with ELISANC/ELISPAR, ELCOMP and ELCOMPN.

But, to reinforce the earlier statement, there is no way you can copy a consolidation onto its children. If achieved, it could mean the end of the world as we know it: inter-dimensional wormholes cropping up everywhere and neutrinos flying faster than the speed of light. Oh, wait...

JayM wrote:I want to spread my consolidated element value to all its children. Is there anyway to do this?

This is fairly easy to do. Create a TI process where you put a value of 1 in every child of the parents you want and then another TI that does a CellPutProportionalSpread to the parents. This will take the value of the parent and put an equal amount in each child. I've used this technique many times.

qml wrote: Can you elaborate what you mean by this? Where does the value sit? Consolidations in TM1 cannot store numeric values, so in order to "have" any values they have to have them calculated, either by consolidating, or by a rule-driven computation. Now, if you want your consolidated value which is (typically) a weighed sum of all its children copied onto all of its children which in turn roll up to the consolidation which is copied onto all its children which... Well, you get it. It has circular reference written all over it.

Yes I have a value at consolidated level which is a sum of all its children elements, where some of the children has value and some have 0s. I would like to equally spread the value at the consolidated level to all its children so that all the children have some value and none of them have 0s.
for example:
Consolidated Value: 100 (from 10 children)
where
Child 1: 10
Child 2: 00
Child 3: 10
Child 4: 00
Child 5: 00
Child 6: 20
Child 7: 00
Child 8: 10
Child 9: 00
Child 10: 50

I would like spread or distribute the value 100 such that all the children have a value of 10.

qml wrote: But, to reinforce the earlier statement, there is no way you can copy a consolidation onto its children. If achieved, it could mean the end of the world as we know it: inter-dimensional wormholes cropping up everywhere and neutrinos flying faster than the speed of light. Oh, wait...

But concluding from the above statement I think its impossible to change the course of action or we are looking at one big disaster

JayM wrote:But concluding from the above statement I think its impossible to change the course of action or we are looking at one big disaster

I gave you the strategy.

Create a view of just the consolidated nodes in the cube you want to do this for.
Use this view in a TI process to do the spread.

The Data tab in your TI would do this.

1) Read record.
2) Look at node, get number of children in node, loop thru children, placing a value of 1 in each.
3) Do CellPutProportionalSpread using value in view and node in view.

This will replace every child with an equal amount from what the parent used to have. In your example, each child would be 10 after you ran the process.

Oh, and BTW, in case you haven't figured it out yet. This can only be done in a TI process, not a rule.