Enhancing Logic

Post Reply
Sergio85
Posts: 4
Joined: Thu Jan 11, 2018 4:33 pm
OLAP Product: Planning Analytics 2.0
Version: TM1 64bit
Excel Version: Excel 2013

Enhancing Logic

Post by Sergio85 »

I am a relatively new user and I was looking for help updating logic in a cube I have created with tm1.

I have a cube with 6 Dimensions:

Versions - contains different version types I archive to
Amount - self explanatory
Fiscal Year - Starts at 'FY 16' and ends around 'FY 22'
Category Items - contains 6 different 'Pay group' elements
Month_Cumulative - contains 2 elements: 'Element' & 'Cumulative'
Months for FY - Contains 12 elements that are months abbreviated: Stats with 'Oct' goes in ascending order till it reaches 'Sep'

I have created a rule file self-escalates the 'Cumulative' data total based previous month's 'Cumulative' data value, on an element level, for each 'Pay group' (We can say for this example: Pay Group A, Pay Group B.. etc) element. It begins with data in 'Oct' and ends in 'Sep'. 'Oct' data for 'Cumulative' is set to equal the 'Monthly' data for 'Oct'. 'Nov' 'Monthly' data is then added to 'Oct' 'Cumulative' data value, which then gives you the 'Cumulative' data amount for 'Nov' and continues on, if that makes sense. The data for the 'Monthly' element in the 'Month_Cumulative' dimension is prepopulated based on the data that is loaded into the cube. This element's data (pertaining to 'Monthly) is random and follows no trend. So this is more based around reciprocating data for the 'Cumulative' element.

The rule file/syntax I have created so far is as follows:
['Oct','Cumulative']=N:['Oct','Monthly']; let's say 'Oct' 'Monthly' is '5', which gives you '5' for 'Oct' 'Cumulative'.
['Nov','Cumulative']=N:['Oct','Cumulative']+['Nov','Monthly']; 'Oct' 'Cumulative' = '5', 'Nov' 'Monthly' = '10', so 'Nov' 'Cumulative' = 5 + 10 = '15'
['Dec','Cumulative']=N:['Nov','Cumulative']+['Dec','Monthly']; 'Nov' 'Cumulative' = '15', 'Dec' 'Monthly' = '20', so 'Dec' 'Cumulative' = 15 + 20 = '35'
['Jan','Cumulative']=N:['Dec','Cumulative']+['Jan','Monthly']; 'Dec' 'Cumulative' = '35', 'Jan' 'Monthly' = '15', so 'Jan' 'Cumulative' = 35 + 15 = '50'
['Feb','Cumulative']=N:['Jan','Cumulative']+['Feb','Monthly']; and so on......
['Mar','Cumulative']=N:['Feb','Cumulative']+['Mar','Monthly'];
['Apr','Cumulative']=N:['Mar','Cumulative']+['Apr','Monthly'];
['May','Cumulative']=N:['Apr','Cumulative']+['May','Monthly'];
['Jun','Cumulative']=N:['May','Cumulative']+['Jun','Monthly'];
['Jul','Cumulative']=N:['Jun','Cumulative']+['Jul','Monthly'];
['Aug','Cumulative']=N:['Jul','Cumulative']+['Aug','Monthly'];
['Sep','Cumulative']=N:['Aug','Cumulative']+['Sep','Monthly'];

I feel like there is a better way to write this rule, that is not as long or takes up as many lines, any suggestions?
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Enhancing Logic

Post by Wim Gielis »

Hello,

The length or number of statements is not so important.
You can condense the notation by omitting the month but using an attribute to hold the previous month (and also one to hold the next month in order to feed the rule). Use DB() to refer to the previous month's value based on the attribute.

Now, usually you solve this cumulative calculation within the structure of the Month's dimension with consolidated elements.
Like that you won't need any rule or feeder and it's extremely fast...
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply