It's important to know how to handle null values in TM1

By default a numeric value in TM1 is 0. If an attempt is made to store a zero in a cell of the cube, that storage request is ignored. If we input 0 for an intersection, TM1 treat it as NO value instead of 0 value. Instead we can use the UNDEFVALS function. This is to be defined in the Rules file which will enable the end user to input 0 value.

Scenario

Let’s create a simple cube with 3 dimensions

Cube Name: Test

Dimension:

1. Actvsbud
2. Region
3. Month

Fill up dummy values in cube for the intersection, Actual, all the months and United States Region

Open Rule file for Test Cube and type UNDEFVALS;

Input the 0 at the intersection of Actual, Oct and United states

We can notice that after updating the Rule file. The Intersection of Actual, Oct and United states is showing 0 even after enabling Zero Suppression

By Keying the “4.94066e-324” intersection, we can make an intersection as NO Value (NULL)

Notes:

1. CubeClearData TI Function will clear all the data in a cube. This function will reset the intersections to NO Value (NULL)

      2. ViewZeroOut TI function sets all data points in a view to zero. This will NOT reset the intersections to NO Value (NULL)

      3. IsUndefinedCellValue TI Function can be used to check the UNDEFVALS is used in the cube or not.

Refer below code to check a cube been used UNDEFVALS and actual value for UNDEFVALS.

Try out our TM1 $99 training 
Join our TM1 training session from the comfort of your home