TM1 Forum

Hello,

I have to use stock accounts (ie : amounts of money) in a TM1 cube but I don't really know how to aggregate them on TotalYear.
The only way I found is to use a numeric attribute on my Period dimension called 'CurrentMonth'.
On this attribute I put 1 on the CurrentMonth, then I can calculate TotalYear in a rule as below :

['TOTYEAR'] = C:
IF ( ATTRS('OBP_Accounts', !OBP_Accounts, 'Type') @= 'STOCK',
IF (['M12'] = 0,
['M01']*ATTRN('ALL_Period', 'M01', 'CurrentMonth')
+['M02']*ATTRN('ALL_Period', 'M02', 'CurrentMonth')
+['M03']*ATTRN('ALL_Period', 'M03', 'CurrentMonth')
+['M04']*ATTRN('ALL_Period', 'M04', 'CurrentMonth')
+['M05']*ATTRN('ALL_Period', 'M05', 'CurrentMonth')
+['M06']*ATTRN('ALL_Period', 'M06', 'CurrentMonth')
+['M07']*ATTRN('ALL_Period', 'M07', 'CurrentMonth')
+['M08']*ATTRN('ALL_Period', 'M08', 'CurrentMonth')
+['M09']*ATTRN('ALL_Period', 'M09', 'CurrentMonth')
+['M10']*ATTRN('ALL_Period', 'M10', 'CurrentMonth')
+['M11']*ATTRN('ALL_Period', 'M11', 'CurrentMonth')
+['M12']*ATTRN('ALL_Period', 'M12', 'CurrentMonth'),
['M12']
),
CONTINUE
);

I am not very satisfied with this solution, so I wondering is someone has already fix this issue.

Thank you

StoneDX

Answer to your query really depends on what you want to report on. Essentially you're creating a rule based on your Month Dimension to calculate something defined in your stock dimension.

Rather than going down the attribute path in your OBP_Accounts dimension, it would be more straight-forward to create a consolidation (called 'stock accounts') in the OBP_Accounts dimension with only those accounts deemed as 'stock accounts'.

Thank you belair22 for your response.

I understand what you mean, but I think I don't explain very well what I want (sorry, i am not an native english speaker).
What I want is to have on my TotalYear element the last non zero month of my accounts which have the 'stock' value on their 'type' attribute.

Example :
For ACC_1 (with STOCK attribute)
JAN = 10
FEB = 12
MAR = 15
APR = 0

TOTYEAR should be 15 (last non zero month)

For ACC_1 (with no STOCK attribute)
JAN = 10
FEB = 12
MAR = 15
...
TOTYEAR should be the sum(JAN to DEC)

Hi StoneDX,

Some ideas to try and remove the need for the C level rule and the flagging of the Current Month. There is nothing that wrong with what you have done IMO though, is there a specific reason why you are not satisfied?

Anyway a different approach

1. Rebuild your cube with another dimension, called Measure.
2. In the measure dimension put two elements, Stock and Applied Stock.
3. Load all your data to Stock.
4. Add a next period attribute to your period dimension.
5. Add the following rule

[Applied Stock']=N:
#Special case where we take the last value
If (ATTRS('OBP_Accounts', !OBP_Accounts, 'Type') @= 'STOCK'

If (
#See if there is any stock in the next period, this should imply that this month is the current month
#Note this logic fails if 0 stock is valid and you will have to use a current month flag test instead.
DB (ThisCube , !DimRefs , 'Applied Stock', Attrs ('ALL_Period', !ALL_Period, 'Next'), !Dimrefs)=0 ,
#Link to stock
['Stock'],
#Else we take 0 since the next value in the month is non-zero and so we can't be in the current period
0),
#Else general case where we want the values to aggregate
['Stock']);

Feeder;
['Stock']=>['Applied Stock'];

6. This should result in just one value in the Applied Stock element for stock accounts and all values for non-stock.
7. So the TotYear consolidation will return the correct result without any further additional work.

Anyway I hope this helps give you some ideas...

Cheers,

Thank you Steve for your clear answer.
I'm going to try with your idea.

Thank you again