Product:
TM1
Issue:
How create a rule?
Solution:
Check out this from https://exploringtm1.com/3-types-of-tm1-rules-all-developers-should-know/
3 types of rules which every consultant should know:
- Allocation/Phase/Spread Rule – e.g. Allocate/phase/spread our budgeted sales across States based on the Actual Sales ratio.
- Rolling Value Rule – e.g. Opening (Measure) is equal to the Closing of the prior period. Often used in the Balance Sheet or Depreciation rules.
- Averaging Rule (C Level) – e.g. Averaging Percentages or Rates up all hierarchies within the cube.
As with TM1, and Platform Software in general, there are a million ways to do anything, so don’t worry if we don’t follow the methodology you are familiar with. That being said, these 3 TM1 rules are a great guide for any developer!
Allocation/Phase/Spread Rule – How to Spread a value Across Periods
This is a common requirement often seen in budgeting rules to allocate/phase/spread an annual budget across months based on Calendar Days, Working Days or Last Year’s Actual Values for the given account.
Here is a sample rule which will phase an annual budget across months based on the number of working days in each month.
[{'Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun'},'Budget','$'] = N:
IF( ['Annual'] <> 0
,IF( DB('General Ledger', !Year, 'Annual', !Scenario, !Department, !Account, 'Phasing') @= 'Even Phasing'
,['Annual'] \
DB('Assumption', !Year, 'All Months', 'Actual', 'Unspecified Department', 'Working Days') *
DB('Assumption', !Year, !Month, 'Actual', 'Unspecified Department', 'Working Days')
, CONTINUE )
, CONTINUE ) ;
There is a number of different ways to write this. For example, I could exclude the {} Months from my rule filter (scope) and filter using an ELISANC within an IF Statement to check that the month element of the current cell being calculated is a descendant of the ‘All Months’ Element but that would clutter the rule tracer when/if used later on the ‘Annual’ Element.
Feeder for Allocation/Spread Rule
Don’t you need a complex feeder with a rule like this? No. We only have to calculate a month if there is a value in the ‘Annual’ month (which is a posting element for annualised data) within the same year. Which means our feeder can simply be:
[‘Annual’,’Budget’,’$’] => [‘All Months’];
If you were writing this longhand, it would look like this:
[‘Annual’,’Budget’,’$’] => [‘Jan’], [‘Feb’], [‘Mar’], [‘Apr’] … [‘Dec’];
Rolling Value TM1 Rule
Calculating a Balance Sheet, Net Book Value or Depreciation? This rule logic is bound to come up. This methodology is going to be slightly different depending on how you have your Time Dimension(s) set up within your cube.
Firstly there are even more possible solutions here, but we are aiming for a sustainable example. This means we will be avoiding DIMNM(DIMIX()-1) in favour of using attributes to help move around periods.
We’ll take a customer subscription calculation as our example. We’ll assume the cube for this rule has a separate Year and Month dimension.
Measures Dimension for Rolling Rule
Given a measures dimension which looks like this:
- Closing Subscriptions
- Opening Subscriptions
- New Subscribers
- Subscriber Churn (Displayed as a positive sign, aggregated with a -1 Weighting)
Attribute used on Month Dimension
We then have a clever little attribute table on the Month Dimension:
Rolling Rule
A TM1 rule can then be written which looks like this:
[{'Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun'},'Forecast','Opening Subscriptions'] = N: DB('Subscription'
,STR(NUMBR(!Year) - ATTRN('Month',!Month,'Prior Year Component'),4,0)
,STR(NUMBR(ATTRS('Month',!Month,'Month Number')) - ATTRN('Month',!Month,'Prior Month Component'),2,0)
,!Scenario,!Department,!Product,'Closing Subscriptions');
This is what I would class as a bare-bones rule for Rolling a Value. This should go back as far as the Year dimension’s elements will go and has not potential to create a circular reference like DIMNM(DIMIX()-1) methodology.
If you want to post an opening amount into the first month and first year within your TM1 cube you can use an additional check to see if the generated Year exists using the DIMIX function, if it doesn’t a STET will make the cell editable.
[{'Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun'},'Forecast','Opening Subscriptions'] = N:
IF( DIMIX('Year', STR(NUMBR(!Year) - ATTRN('Month',!Month,'Prior Year Component'),4,0)) = 0
,STET
,DB('Subscription'
,STR(NUMBR(!Year) - ATTRN('Month',!Month,'Prior Year Component'),4,0)
,STR(NUMBR(ATTRS('Month',!Month,'Month Number')) - ATTRN('Month',!Month,'Prior Month Component'),2,0)
,!Scenario,!Department,!Product,'Closing Subscriptions')
);
Feeder for Rolling Rule
Then, the feeders for this involve the same amount of coding, but the theory may be daunting for people still learning. This is because where the rule went back across time periods to get the value, the feeder has to go forwards across time periods to push the value into the rule calculation cell.
[{'Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun'},'Forecast','Closing Subscriptions'] =>
DB('Subscription'
,STR(NUMBR(!Year) + ATTRN('Month',STR((NUMBR(ATTRS('Month',!Month,'Month Number'))-13)-1,2,0),'Prior Year Component'),4,0)
,STR(NUMBR(ATTRS('Month',!Month,'Month Number')) + ATTRN('Month',STR((NUMBR(ATTRS('Month',!Month,'Month Number'))-13)-1,2,0),'Prior Month Component'),2,0)
,!Scenario,!Department,!Product,'Opening Subscriptions');
This could be written simpler if we didn’t piggyback the same “Prior Year” Attributes and instead added new “Next Year” Attributes. The above example feeder has a minimalistic approach to attributes but is paying for it in rule complexity.
I am also using a filter of each month because I have other N level elements in my month dimension which I don’t want this rule applied to.
Averaging Rule (C Level)
C Level (Consolidation Level) TM1 rules which do averaging are very similar to normal rules but the reason we have them listed is that people don’t realize until they have to write one that a (non-zero value) countermeasure is needed and you need to use a separate measure to perform the calculation in most cases.
Legacy Method
For averaging a value based on a counter.
['Average Price'] = C: ['Price'] \ ['Product Count']; ['Product Count'] = N: IF( ['Price'] > 0 , 1 , 0 );
Feeder:
['Price'] => ['Product Count'], ['Average Price'];
I’m using price and not units for my product counter as I want to average all prices regardless of if the product is sold in a specific period. However, if I wanted an average price pro-rata units sold I would back solve my revenue equation like so.
['Average Price'] = C: ['Subscription Revenue'] \ ['Closing Subscriptions'];
Feeder:
['Closing Subscriptions'] => ['Average Price'];
New Function Method
Averaging based on the data within a measure. Here we use the ConsolidatedAvg function.
['Average Price'] = ConsolidatedAvg (2, 'Subscription', !Year, !month, !Scenario, !Department, !Product, 'Price');
Feeder:
[‘Price’] => ['Average Price'];
The first argument of the ConsolidatedAvg function can be set as either:
- 0 – consider all cells while averaging.
- 1 – use weightings
- 2 – ignore blank cells (zero values) while averaging
- 3 – use weights and ignore blanks
To learn more about TM1 – go to https://tm1explorers.com/webinars/
