IF() TM1 Rule Function: Use and Syntax

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 Rules

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

If ( test, statement1, statement2 );

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. Obviously we can keep on nesting.

Use of IF() in 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 rule.

An example of the use of IF is as follows:

['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…

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

If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.
This entry was posted in Rule Functions. Bookmark the permalink.

One Response to "IF() TM1 Rule Function: Use and Syntax"

Log in to post a comment.