I think this is a bug. But after searching around a bit, I don't see anybody else complaining about it, so maybe it is by design. But why anybody would want it that way ...
My user wanted to increase a future budget by 2% over a range of accounts, cost centres and periods.
I had them do a spread, using 2% Add.
But what TM1 actually did surprised the heck out of me.
Negative numbers moved toward zero, instead of away.
e.g. 1000 and -1000 after a 2% Add became 1020 and -980
I expected it to be 1020 and -1020
(The correct incantation is to do a 102% Replacement.)
Undoing this change was quite trickey, because no single transformation would put the numbers back the way they were. I had to write a TI process to operate on just the negative numbers.
So, I got the problem fixed, but the next person who wants to do a spread to increase positive and negative numbers could easily make the same mistake.
(9.5.2)
Spread % Change add
-
Duncan P
- MVP
- Posts: 600
- Joined: Wed Aug 17, 2011 1:19 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2
- Excel Version: 2003 2007
- Location: York, UK
Re: Spread % Change add
I think you are right. 2% of a negative number is a negative number.
-
EvgenyT
- Community Contributor
- Posts: 324
- Joined: Mon Jul 02, 2012 9:39 pm
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: 2016
- Location: Sydney, Australia
Re: Spread % Change add
Are you using Percentage Change method for data spread?
If you use update action: subtract on negative numbers you get e.g -1020 for -1000 . . . its not ideal, but seems like a work about (only for negative numbers thou)...
ET
If you use update action: subtract on negative numbers you get e.g -1020 for -1000 . . . its not ideal, but seems like a work about (only for negative numbers thou)...
ET
-
pmakulski
- Posts: 60
- Joined: Mon Jun 06, 2011 6:07 pm
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2010
Re: Spread % Change add
EvgenyT:
Yes, it should come out to -1020 but if you try it (% Change add 2%) you'll see that it comes out as -980.
2% of -1000 is -20. But it doesn't add it to the original (which would/shoud be -1000 + -20 = -1020), it adds the absolute value (so -1000 + abs(-20) = -980 )
DuncanP:
Yes I was using %Change add.
I agree, %Change subtract would give the correct value for negative numbers, but it has the same problem as %Change add, whenever it is applied to a mixed collection of positive and negative numbers.
The only thing that works reliably/correctly for mixed collections is %Change replace (ie. 102% for 2% increase or 98% for 2% decrease).
I'd like to be able to configure the spread options available to users so that add and subtract options are not even available, since they yield incorrect irreversible spreads. Or better yet, correct the calculation they perform. (Unless there is a legitimate reason for wanting to increment positive numbers and decrement negative numbers. - I can't think of a reason anyone would want to do that.)
Yes, it should come out to -1020 but if you try it (% Change add 2%) you'll see that it comes out as -980.
2% of -1000 is -20. But it doesn't add it to the original (which would/shoud be -1000 + -20 = -1020), it adds the absolute value (so -1000 + abs(-20) = -980 )
DuncanP:
Yes I was using %Change add.
I agree, %Change subtract would give the correct value for negative numbers, but it has the same problem as %Change add, whenever it is applied to a mixed collection of positive and negative numbers.
The only thing that works reliably/correctly for mixed collections is %Change replace (ie. 102% for 2% increase or 98% for 2% decrease).
I'd like to be able to configure the spread options available to users so that add and subtract options are not even available, since they yield incorrect irreversible spreads. Or better yet, correct the calculation they perform. (Unless there is a legitimate reason for wanting to increment positive numbers and decrement negative numbers. - I can't think of a reason anyone would want to do that.)