IsUnd function in TM1


When you frequently use tools like Excel or TM1, you know that you will use the most common functions in a relatively short period of time. But you also realize that it will take you quite a while before you will have used the other say 5% of functions. Today is such a case, where I used the function IsUnd for the very first time in almost 10 years of TM1.

The case is as follows. A typical TI process copies data from cube A to cube B. For instance, B is a reporting cube on top of A. One measure is part of the data source in cube A, and using a loop over the measures in the last dimension of cube B, we do our usual CellGetN/S and CellPutN/S stuff.

But some measures in the source cube where rules-calculated and ended up to be an error value in the cube. The customer asked me to skip these "bad" cells and would correct the calculation later on. Hence, I needed a way to skip these cells, errors were only found in numeric cells. If you do CellGetN towards a cell containing an error, this generates an error in the message log. We want a way to skip these messages and continue with the next measure/cell.

Sample code

# Loop over measures
m = 1;
While( m <= Dimsiz( 'Sales_Rpt_Measure' ));

   vMsr = Dimnm( 'Sales_Rpt_Measure', m );

   If( Dtype( 'Sales_Rpt_Measure', vMsr ) @= 'C' );

   ElseIf( Dtype( 'Sales_Rpt_Measure', vMsr ) @= 'N' );

      If( IsUnd( CellGetN( 'A', vProduct, vYear, vMsr )) = 0 );
         CellPutN( CellGetN( 'A', vProduct, vYear, vMsr ), 'B', vProduct, vYear, vMsr );

   ElseIf( Dtype( 'Sales_Rpt_Measure', vMsr ) @= 'S' );

      CellPutS( CellGetS( 'A', vProduct, vYear, vMsr ), 'B', vProduct, vYear, vMsr );


   m = m + 1;


As you can see near the middle of the code, I loop over the measures dimension in cube 'B' (which contains elements that are also found in cube 'A'). IsUnd gives us 0 or 1. When it is not 1 I skip that measure.


Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links