Hold and Proportional Spread

Post Reply
RN4ever
Posts: 7
Joined: Thu Oct 03, 2019 10:44 am
OLAP Product: Planning Analtycs
Version: 2.0.8
Excel Version: 2013

Hold and Proportional Spread

Post by RN4ever » Fri Mar 13, 2020 5:22 pm

Hi all,

In my cube with Customers , Year/Month dimensions and measures(revenue ,cost and others) I would like, by a simple TI process, copy values from a generic measure A and paste it into Cost measure.

So, I wrote a process which uses as data source a view for the Measure A where a few values are different by null or zero and, for each iteration in the Data tab, perform a CellPutProportionalSpread on the Cost measure.

Because I don't want modify the Year consolidated value, I hold by process, the year consolidated cell

But, when, after hold the year consolidated cell, i perform the first spread on January, for example, I noticed, logging with ASCII output using the hold cube created for my user, that Year consolidation is deleted! :cry: (the cell in the hold cube is not 'C' but it is null)

If I comment the CellPutProportialSpread line I find all the cells exptected as hold (so, it is good)
If I uncomment that line, cells are not hold and values is incorrect because the year value is changed (because the hold is lost)

Have you experience with this strange behavior?

Below I put my simple code:

Code: Select all

CUB = 'My CUBE';
Dim = 'Time';
Hold = '}Hold_' | TM1User() | '_}}_' | CUB;

# Copy the value
v=CellGetN(CUB, Customer, Time, 'Measure A');

# Remove the possible hold on the destination
CellPutS('', Hold, Customer, Time, 'Cost', 'HoldStatus');

# Proportional Spread on the destination
CellPutProportionalSpread( v, CUB, Customer, Time, 'Cost');

#Hold the Cost value
if(DTYPE(Dim, Time)@='N');
	s='H';
else;
	s='C';
endIF;

CellPutS(s, Hold, Customer, Time, 'Cost', 'HoldStatus');
Thanks in advance for you precious advices

Regards

User avatar
paulsimon
MVP
Posts: 739
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Hold and Proportional Spread

Post by paulsimon » Fri Mar 13, 2020 7:27 pm

Hi

You seem to have included pseudo code and haven't given the source view so it is hard to understand the issue.

What is the business problem that you are trying to solve?

It seems like you want to phase some overall cost over months without changing the year total, presumably according to some pattern such as prior year spending. If that is the case then I would advise against using spreading. Instead just take year level measure * value in period last year \ value for full year last year. That will be more reliable.

I have been developing with TM1 for decades and I almost never use spreading because users tend to find it too difficult to understand, as it presents too many options, and it is too easy to make serious errors. Instead I invariably put in rules or TI to give the users the specific type of spreading that they want. That is not to say that you should never use it, but in my opinion, it is perhaps best used by a very small number of users in the early stages of high level top down planning

Regards

Paul SImon

RN4ever
Posts: 7
Joined: Thu Oct 03, 2019 10:44 am
OLAP Product: Planning Analtycs
Version: 2.0.8
Excel Version: 2013

Re: Hold and Proportional Spread

Post by RN4ever » Sat Mar 14, 2020 10:01 am

paulsimon wrote:
Fri Mar 13, 2020 7:27 pm
What is the business problem that you are trying to solve?
I need to perform, by a button in a PAW book, so using a TI process, a "copy & paste" of values from a measure to another using hold in order to "lock" values at the top level and using spread on the cells on which I don't paste values.

Many thanks for your time

Regards

RN4ever
Posts: 7
Joined: Thu Oct 03, 2019 10:44 am
OLAP Product: Planning Analtycs
Version: 2.0.8
Excel Version: 2013

Re: Hold and Proportional Spread

Post by RN4ever » Tue Mar 17, 2020 5:03 pm

I'm sorry for my "up" but unluckly i haven't yet found a solution...
I verified more times that If I comment the CellPutProportionalSpread statement, cells are hold as I exptected.

If I use the statement, uncommenting the line, the cell are not more hold. Only the last processed cell, in the Data tab, is hold

Why??? :(
I don't understand....

Thank you for your time

Wim Gielis
MVP
Posts: 2366
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Hold and Proportional Spread

Post by Wim Gielis » Tue Mar 17, 2020 5:15 pm

EDIT: go with the advice of Timok below.


I have not done this yet in TI but it sounds like a bug if you tested this thoroughly.
Last edited by Wim Gielis on Tue Mar 17, 2020 5:27 pm, edited 1 time in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

tomok
MVP
Posts: 2702
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Hold and Proportional Spread

Post by tomok » Tue Mar 17, 2020 5:15 pm

How can you expect anybody to help when you haven't provided the actual code. Pseudo code is worthless, plain and simple.
RN4ever wrote:
Tue Mar 17, 2020 5:03 pm
I verified more times that If I comment the CellPutProportionalSpread statement, cells are hold as I exptected.
This one is kind of a no-brainer. According to your provided code, if you take out the CellPutProportionalSpread then you aren't actually changing anything so how do you know it got "held"? You never attempted to change anything so there was nothing to hold. If, on the other hand, the pseudo code is not actually what you are really doing then who knows since you haven't shared that with us.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

declanr
MVP
Posts: 1642
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Hold and Proportional Spread

Post by declanr » Tue Mar 17, 2020 5:49 pm

Like the others have said actual code would be could but assuming the pseudo code you have provided is correct then I expect your data source is the problem.

As I understand it:
1/ You clear out holds on the cell (so that the proportional spread doesn't fail)
2/ You proportionalspread a data cell
3/ You apply a hold (Consolidated or Leaf hold based on whether your time element is N or C)

And I think that your proportional spread is working but your third step is not.

First make sure that you are flagging all the correct things at datasource (not skipping consolidations.)

Second look at your "H" or "C" logic.
You are applying leaf level hold when the time dimension is N-level.
This would make sense... unless your Customer dimension is at C-level; in which case the hold will not do anything. Putting a "H" against a consolidated customer will make the "red corner" appear (in some interfaces) but it won't actually do anything. Either you would still want that as a "C" or you would need to loop all of the "n" level customers under it to do the equivalent of "Apply Hold to all Leaves".
Declan Rodger

Post Reply