RIGHT TM1 Function: Use and Syntax

The RIGHT function from Excel does not exist in TM1. We need to use a combination of SUBST and LONG functions instead.

Where you have a formula in Excel in A2 that is = LEFT ( A1, 4), where the contents of A1 is the string “ExploringTM1”, the formula will return the first 4 characters, i.e. “Expl”.

To achieve the same result in a TM1 Turbo Integrator process we need to use the SUBST and LONG functions. A full discussion on SUBST can be found here and LONG here, however briefly, the formula to replicate the Excel function illustrated above would be SUBST ( ‘ExploringTM1’, LONG ( ‘ExploringTM1’) -4 +1, 4);

In other words:

  • Take the string (or the variable referred to that contains the string) and set the starting at position as the length of the string (‘ExploringTM1’, which is 12 characters).
  • Subtract 4 (for the length you want to return) and add 1 (so the starting position is actually the 9th character),
  • Then take the next 4 characters (including that first character),

Which will return ‘gTM1’.

This applies to both Rules and Turbo Integrator processes.

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 site uses Akismet to reduce spam. Learn how your comment data is processed.