TM1 Forum

Hello everyone,
I am new to TM1, I have no background whatsoever in IT, and I apologize in advance for the length of this post.
I am trying to tweak an otherwise functional TI process that uses a Text Datasource to upload financial data in a cube.
My problem is with a variable of the process; let’s call it ‘Account’.
Here is the issue: when the balance of an account goes to 0, that account will be totally absent from the source files generated (the amounts are Year-To-Date).
Thus, TM1 is not being fed the information that the balance is now 0, and the prior period balance erroneously carries forward.

There is already coding in place in the Metadata tab to add new elements to the dimension based on the source file:
IF ( DIMIX (‘Accounts Dimension’ , ‘Account’ ) = 0 );
DIMENSIONELEMENTINSERT(‘Accounts Dimension’, '',’Account’,'N');
DIMENSIONELEMENTCOMPONENTADD(‘Accounts Dimension’, '',’Account’,'1');
ENDIF;

I mention this because I am now trying to do kind of the opposite.
My goal is to come up with a code that does the following:

If any dimension element = found in source file, ‘Amount’ is uploaded from source file
If any dimension element = NOT found in source file, ‘Amount’ = 0

I added the following to the Data tab. It's the closest I got to a solution.
IF(DIMNM(‘Accounts Dimension’, DIMIX(‘Accounts Dimension’,’Account’))@=’Account’);
‘Amount’=’YTD balance’;
ELSE;
‘Amount’=0;
ENDIF;

This logic is flawed because the element component in the DIMIX function is limited to only accounts that are in the source file.
Hence, dimension elements not included in the source file are not subject to this function, which defeats the very purpose of what I am trying to do.
What I need is a way to compare ALL dimension elements against the ‘Account’ variable.
I tried multiple math tricks to make up for it without success.
Eg:
'Element': Some kind of subset of all dimension elements ??
IF(DIMNM('Accounts Dimension', DIMIX(‘Accounts Dimension’, 'Element'))@=’Account’);

Or some more desperate attempts thinking I could create a variable equal to any number using RAND:
'Index'= 'RAND()*2+RAND()*3+RAND()*4' |'RAND()*2+RAND()*3+RAND()*4' |'RAND()*2+RAND()*3+RAND()*4';
IF(DIMNM(‘Accounts Dimension’, 'Index')@=’Account’);

Is there a way do this?

Hoping that my explanations were clear.
Thanks in advance

The best way to do this would be to not do anything to the process that loads data from the text file.

Either you find a way to make the source system include records for accounts which had a balance but are now 0 in which case you don't need to do anything at all to any TI.

Or if there is no way to influence the file then create a NEW process based on a zero suppressed view of the last month and CellPutN in a zero for all those accounts to the current month. Make this new process the prior step in the load job to the existing process. Then any account which previously had a balance but is zero will now be zero in your cube. No change to result for accounts with balances as they will be set to the right value from the file in the next step by the existing process.

This will be 1000x easier and more efficient and way more foolproof than any attempt to do it all in the current load job.

Note depending on how your carry forward rules are written the "zero" might need to not be a true null but some really tiny value like 0.0000000001

Very clever. Thanks lotsaram