TM1 Forum

Guys,

There is the TI function called CellPutProportionalSpread. Do you know if there an undocumented function within TI that will do Relative proportional spreads?

Thanks,

Jim.

jim wood wrote:Guys,

There is the TI function called CellPutProportionalSpread. Do you know if there an undocumented function within TI that will do Relative proportional spreads?

Thanks,

Jim.

Do you mean something like, spread into Column B, based on the values in Column A? If that's the question, then no. However, you can achieve the same results with CellPutProportionalSpread in three steps:
1) Clear column B
2) Load values from Column A into Column B
3) Use CellPutProportionalSpread with total dollar amount of Column A, into top most node in Column B.

... or if you meant "spread a 10% increase across the leaf descendents of ..." you could do ... or if you meant "add 100 to every leaf descendent ..." you are out of luck.

Duncan P wrote:... or if you meant "add 100 to every leaf descendent ..." you are out of luck.

Although not a specific function this still isn't difficult to achieve;

IF(ELISANC(pParent,v1)=1);
CellPutN(100+CellGetN(Cube,v1,v2,v3,v4),Cube,v1,v2,v3,v4);
ENDIF;


You just need to make sure that the datasource doesn't result in a loop or elements appearing more than once.

The only problem is iterating over the leaf descendent cells of the cell to which you want to do this. If at the time that you are writing the TI you know the dimensionality of the cube in which you wish to do it then you can make a set of nested loops, one for each dimension, and progressively iterate over their members. If you are writing a generic TI, like those in Bedrock then it's a lot more difficult - but still possible.

Thanks for the replies guys. You've given me plenty to chew on. I was going to use the elisanc approach but the problem is they want to spread the value across multiple dimensions. While this doesn't rule this approach it does mean coding and testing it will be a lot of fun,

Jim.

An alternative method of iterating might be to create a temporary subset on each dimension with the leaf descendents in (either manually or by MDX), create a temporary view from the subsets and then use the view as the source for a sub-process that uses its process variables as cell coordinates and does the relevant work on the cells. If you wanted to put data into every cell, whether currently populated or not, you would need to call ViewExtractSkipZeroesSet with false.

I think we going to go for the method Tomok mentioned. We already have no data so we don't have to worry about that. What we are going to do is create a numeric attribute for each dimension involved and and calculate a ratio (for the attribute) for every element based on the number of children of it's parent. We can load these values and complete an effective even spread. That's the theory anyway.