IF Rules in TM1: Use, Syntax and Examples

IF() is a function that can be used to test if an expression is true so that you can then differentially execute a rule based on the outcome of the IF statement. It is able to be used in both Turbo Integrator processes and Rules, however this post is about the use of it in Rules only.  For TI based IF statements, please see this post.

Syntax of IF in TM1 Rules

The syntax is almost identical to the IF statement in Excel, in that it is:

If ( test, outcome1, outcome2 );

So in this we are testing if the test is true and if so, then we get outcome1.  If it is not true, then we execute outcome2.

Note that you can nest IF statements in rules, so the syntax of a nested IF statement could be:

If ( test1, outcome1, if ( test2, outcome2, outcome3 ) );

Here we are testing if test1 is true and if it is, then we get outcome1, if test1 is false, then we do test2. If test2 is true, then we get outcome2 and if false, outcome3.

Use of IF in TM1 Rules

Using an IF statement in rules appears simple, yeah? Well it is when you have a single IF, or even two. But when you do multiple nests, then it can become very complex, very quickly. So to get around that, we can use the Continue or Stet functions in the IF and then separate out the nesting into their own independent IF statements.

So, using the example from above we would change:

If ( test1, outcome1, if ( test2, outcome2, outcome3 ) );

to

If ( test1, outcome1, Continue );
If ( test2, outcome2, outcome3 );

Obviously these need to be on the right side of the rules!

Rule to bring Actuals through to a Forecast for Prior Years, Months and Days

An example of the use of IF is as follows (this is a relatively complex nested statement):

['Plan',{'In Stock','On Order', 'Commissioned', 'Committed'}] = N:
  IF (  NUMBR (!Year) < NUMBR ( DB ('System Settings', 'Current Year', 'String') )
    ,['Actual']
    ,IF (  NUMBR (!Year) = NUMBR ( DB ('System Settings', 'Current Year', 'String' ) ) & NUMBR ( ATTRS ( 'Month',!Month,'MonthNum') ) < NUMBR ( DB ('System Settings', 'Current Month', 'String' ) ) 
      ,['Actual']
      ,IF (  NUMBR ( !Day ) < NUMBR ( DB ('System Settings', 'Current Day', 'String' ) ) 
        ,['Actual']
        ,Continue 
      )
    )
  ); 

This is checking if the !Year is less than the definition of Current Year and if true, use Actual. If false, then test if !Year is equal to Current Year and !Month is less than Current Month, then if true, use Actual. If false, test if !Day is less than Current Day and if true use Actual and if not (i.e. beyond Current Day), then Continue onto the next rule.

This rule can be restated as follows using three rules rather than one large rule with multiple nested IF statements:

['Plan',{'In Stock','On Order', 'Commissioned', 'Committed'}] = N:
  IF (  NUMBR (!Year) < NUMBR ( DB ('System Settings', 'Current Year', 'String') )
    ,['Actual']
    ,Continue );

['Plan',{'In Stock','On Order', 'Commissioned', 'Committed'}] = N:
  IF (  NUMBR (!Year) = NUMBR ( DB ('System Settings', 'Current Year', 'String' ) ) & (  NUMBR ( ATTRS ( 'Month',!Month,'MonthNum') ) <   NUMBR ( DB ('System Settings', 'Current Month', 'String' ) ) )
    ,['Actual']
    ,Continue );

['Plan',{'In Stock','On Order', 'Commissioned', 'Committed'}] = N:
  IF (  NUMBR ( !Day ) < NUMBR ( DB ('System Settings', 'Current Day', 'String' ) ) 
    ,['Actual']
    ,Continue );

I’ll let you decide which one is easier to understand!

Just as an aside, yes, I know that I could have written this with a single IF statement using the DayNo function and concatenating Year, Month and Day to test it…

Usage

This version of IF is valid only in Rules only.  Note there is also an IF() statement in Turbo Integrator.

You might also like