Page 1 of 1

Enhancing Logic

Posted: Thu Jan 11, 2018 5:54 pm
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?

Re: Enhancing Logic

Posted: Thu Jan 11, 2018 6:20 pm
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...