TM1 Forum

The function CellPutProportionalSpread will sprinkle a value across all leaf elements in a consolidation but what if we want to align that spread along two axis?

For example,


The body of the tables have inputs for products by quarter. A new edition of the report rolls around so these need to be updated. The analyst knows row and column totals (bright green areas), so these are updated. This new grand total is distributed into the body of the table.

This would be Iterative Proportional Fitting (IPF). I can perform this in Excel by making a series of tables or with a VBA function.

I was curious if there was an out-of-the-box method for this in TM1. There are a variety of spreading techniques in the PAW interface, as shown below, but what about as a function in a procedure?

Have you looked at the TI function CellPutProportionalSpread

https://www.ibm.com/docs/en/planning-an ... onalspread

TomBr wrote: ↑Mon Sep 27, 2021 4:29 pm Have you looked at the TI function CellPutProportionalSpread

https://www.ibm.com/docs/en/planning-an ... onalspread

I looked into that particular function but only it spreads across only one axis. I need to balance inputs against two separate axes. Since TM1 offered CellPutProportionalSpread I was hoping there were similar functions of that nature that expand on how data could be spread. Seems like that's the only one available, sadly.

I was able to accomplish this by creating a separate cube that included an extra dimension for iterations. The formulae for Iterative Proportional Fitting are rather simple but it gets a little more complex handling the dimensional structure. From a perspective of usage/calculation efficiency, this method works quite well since you can throw many measures from the same cube into the same bank of rules.

From a development perspective it is less ideal because each source cube will require a separate cube to handle the IPF iterations. My project will require this in several areas, so it just adds overhead to what I'm building.

Ultimately, I'm just a lazy developer that wants a magical function to solve my issues.

I looked into that particular function but only it spreads across only one axis

Just tested with Sdata and confirmed CellPutProportionalSpread works multidimensionally.

David Usherwood wrote: ↑Mon Sep 27, 2021 7:45 pm

I looked into that particular function but only it spreads across only one axis

Just tested with Sdata and confirmed CellPutProportionalSpread works multidimensionally.

Because OP is looking for an iterative proportional spread, they don’t quite just want proportional.

Rather they are looking for (if I have understood):
1/ go from prod A to D and proportional spread on row total
2/ apply a HOLD on each of the 4 row totals
3/ proportional spread on Q1 for total products
4/ apply a HOLD on Q1, total products (keep old hold)
5/ proportional spread on Q2, total products (keep old holds)
6/ apply a HOLD on Q2, total products (keep old holds)
7/ proportional spread on Q3, total products.

Theoretically Q4 as the only one not held should have also been adjusted accordingly and all holds can be released at that point.

You could look at doing it in TI as you can apply holds by playing with the control cubes… just remember to remove the holds at the end.

declanr wrote: ↑Mon Sep 27, 2021 8:43 pm Because OP is looking for an iterative proportional spread, they don’t quite just want proportional.

Rather they are looking for (if I have understood):
1/ go from prod A to D and proportional spread on row total
2/ apply a HOLD on each of the 4 row totals
3/ proportional spread on Q1 for total products
4/ apply a HOLD on Q1, total products (keep old hold)
5/ proportional spread on Q2, total products (keep old holds)
6/ apply a HOLD on Q2, total products (keep old holds)
7/ proportional spread on Q3, total products.

Theoretically Q4 as the only one not held should have also been adjusted accordingly and all holds can be released at that point.

You could look at doing it in TI as you can apply holds by playing with the control cubes… just remember to remove the holds at the end.

You're on the right track, declanr, that's an interesting idea to apply the IPF process procedurally. I could write that procedure to accept any cube/dimensions/measure. Since TM1 doesn't offer IPF anywhere I'd simply write it m'self. That would eliminate the need for external cubes for iterative computations, too. The only drawback is that the process isn't a live rule.

TM1Py could likely do this via procedure, also, but IBM won't install that package on cloud-based servers.

I wish TM1 had more statistical functionality to accomplish things like this as RULES. I have no idea how it could actually be DONE but I can certainly whinge about it.

TM1Py could likely do this via procedure, also, but IBM won't install that package on cloud-based servers.

TM1Py talks to the TM1 server via the RestAPI - it does not have to be installed on the IBM cloud. Install it on a server (or client) over which you have control.