NUMBR TM1 Function: Use and Syntax

From time to time we need to convert a string (or text) into a value. If we were in Excel, we would use the Value function. Unfortunately, “Value” doesn’t work in TM1. But NUMBR does!

What NUMBR Means

This function converts a string of text that looks like a number to a numeric expression, or value.

Syntax

The syntax is

NUMBR (string);

where string is a value stored as a string (surprise, surprise!).

Usage

An example is NUMBR (‘10.75’) which will return the numeric value 10.75.

This function can be used in both Rules and TI processes

Example

Another example is where we have a Month dimension that uses 1, 2, 3 etc to represent months, or a Year dimension that contains 2017, 2018, 2019 etc. These can be problematic to evaluate in rules and using this function to convert them to values prior to being evaluated using an =, < or > sign, can make this smoother.

Then you could use a rule like the following to determine if an Actual/Forecast flag should show “Actual” or “Forecast”

['Actual/Forecast'] = S: 
  IF( NUMBR(!Year) < DB('System Control','Current Year' , 'Value' )
    ,'Actual'
    ,IF( NUMBR(!Year) = DB('System Control','Current Year' , 'Value' )
    ,IF( NUMBR(!Month) < DB('System Control','Current Month' , 'Value' )
        ,'Actual'
        ,'Forecast')
    ,'Forecast')
    );

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.

One comment on “NUMBR TM1 Function: Use and Syntax

This site uses Akismet to reduce spam. Learn how your comment data is processed.