TM1 Forum

Hi all,

I have created an TI Process in TM1. I have created an export view with one element (Account) at consolidated level. In data tab, I am writing cellgetn formula for different combinations of hierarchies for different accounts using the export view. Though the consolidated Account has value, leaf level account does not have a value, resulting in numerous "0" rows in the data extract. Is there a way I can suppress the 0?

Did you wrap the CellGetN's in an IF statement, to check whether they are different from 0 ?

Yes,

Already did but not working. I am trying to create a multi dimensional extract where x axis has combination of Months and account and Y axis has combination of 5 dimensions.

In export view: I am using "Total account" and "Current month"
In Data tab: Im doing cell get n for 2 accounts for all the months till current month.

Then your IF must be wrong. Or the IF targets the wrong cells.

A sample:



CalcJanAccount1Value = CELLGETN(srcCube , VersionInput1 ,SUBST(pPeriod,1,4) | '01' , vBilling_Code , pAccount1 , '401' , vBusiness_Unit , vBU_Region ,
vMandate , vCounterparty , vProduct , vClient , vReporting_Measure );
CalcJanAccount2Value = CELLGETN(srcCube , VersionInput1 ,SUBST(pPeriod,1,4) | '01' , vBilling_Code , pAccount2 , '301' , vBusiness_Unit , vBU_Region ,
vMandate , vCounterparty , vProduct , vClient , vReporting_Measure );

IF(~(CalcJanAccount1Value = 0));
JanAccount1Value = CalcJanAccount1Value;
ENDIF;

IF(~(CalcJanAccount2Value = 0));
JanAccount2Value = CalcJanAccount2Value;
ENDIF;


IF(SUBST(pPeriod,1,4) | '01' @= pPeriod);

ASCIIOUTPUT(sFileOut,vProduct, vClient, vBusiness_Unit, Numbertostring(JanAccount1Value), Numbertostring(JanAccount2Value), Numbertostring(Account1ROY),Numbertostring(Account2ROY));

ELSEIF(SUBST(pPeriod,1,4) | '02' @= pPeriod);

............................. (written same code for other periods)


ENDIF;

Maybe... take the absolute value and test whether it's smaller than 0.01 for example. 0 is not always 0.

Other than that, use additional AsciiOutput statements to properly debug your code, or use the TI debugger tool provided by IBM.

Thanks:)