<img src="https://trc.taboola.com/1278851/log/3/unip?en=page_view" width="0" height="0" style="display:none">
finding octane

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

0
3 min

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 ...

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.

binary-1254502_640.png

 

Scenario

Let’s create a simple cube with 3 dimensions

Cube Name: Test

Dimension:

  1. Actvsbud
  2. Region
  3. Month

 Null Value6.jpg

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

 Null Value1.jpg

Open Rule file for Test Cube and type UNDEFVALS;

 Null Value2.jpg

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

Null Value3.jpg

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)

Null Value4.jpg

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.

 

Null Value5.jpg

 

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

Find out more

 

Leave a comment

Got a question? Shoot!

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

Get more articles like this delivered to your inbox