From time to time we need to convert a string (or text) into a value. If we were in a spreadsheet, we would use the Excel Value function. Unfortunately, the Excel “Value” function doesn’t work in TM1. But the equivalent to Excel’s Value is NUMBR, and that does!
What NUMBR Means
This function converts a string of text that looks like a number to a numeric expression, or value. From there it can be used in regular mathematics.
Syntax
The syntax is
NUMBR (string);
where string is a value stored as a string (surprise, surprise!).
Example of NUMBR
An example is NUMBR (‘10.75’) which will return the numeric value 10.75, which can obviously then be used in mathematical equations.
Another example is where we have a Month dimension that uses 1, 2, 3 etc to represent months, or a Year dimension that contains 2019, 2020, 2021 etc. These can be problematic to evaluate in rules and using NUMBR 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”. Not this is combined with an IF:
['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'));
NUMBR vs StringToNumber
There are actually two functions in TM1 that do very similar things. NUMBR and StringToNumber. The primary difference is that NUMBR can be used in both Rules and Processes, while StringToNumber can be used in TI processes only. The two functions, other than that, are identical, with the same syntax.
Use in System Control Cube
We at Infocube often use this function in a System Control cube to store all sorts of core information. Things like the Current Month, or Current Year. For these we will often do the entry in, say a String (or Text) field and then have Numeric (or Value) version of it right alongside the string. So we’d have a simple rule for Current Week that says (note it has to use a DB cos it’s evaluating a string!):
['Current Week','Value'] = N: NUMBR(DB('System Control','Current Week','String'));
Usage
This function can be used in both Rules and TI processes
