TI Process Calculation - Catch All Rule?

Post Reply
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

TI Process Calculation - Catch All Rule?

Post by michaelc99 »

Good Afternoon Everyone,

I am in the process of writing one or (potentially) multiple TI processes to replace an Allocation Spreadsheet. The spreadsheet performs A divided by B calculations for specific channels and pushes the results into a separate cube. I am able to complete those calculations without any issues. However, in the Excel document there is a catch-all calculation that performs: =IF(SUM(D21:M21)<>0,1-SUM(D21:M21),0). The catch-all formula is proving to be a bit of a head-scratcher.

I wrote the code below to take "One minus the Control Total of the Core Business product line", then push that plug number back to an OTHER product line category. So, if the sub-total of the remaining allocation lines total 0.95 (95%) then I would expect 0.05 (5%) to fall under the OTHER product line.

Code: Select all

 if( sProdAllocLine @= 'OTHER' );
    OTHERPCT = (1- (CellGetN( 'PRODUCT LINE ALLOCATIONS - MAIN', vPERIOD, 'CORE BUSINESS', 'CONTROL TOTAL')));
    CELLPUTN(OTHERPCT, 'Product line Allocations - Main', vPERIOD , 'Core Business', 'OTHER');
 ELSE;
    CELLPUTN(WWCOREBUSINESSPCT, 'Product line Allocations - Main', vPERIOD, 'Core Business', sProdAllocLine);
 endif;
However, the result of the TI process effectively has not changed the result of OTHER and left a zero (or null) balance. I also used SubsetMDXGet() to pull in the product lines where I attempted to put them in a specific order. When using that function, could the queried items index position change? Meaning, let's assume I build a subset for YEAR with (2021, 2022, 2023) in that order. When SubsetMSXGet() is triggered, would the order always stay (2021, 2022, and 2023) or could that systemically change?

Thank you!
Michael
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: TI Process Calculation - Catch All Rule?

Post by michaelc99 »

Actually, looking at the results closer, it appears to be putting in a -0 value under OTHER. I am looking at my syntax to see if I put too many parenthesis or if my logic is written incorrectly somewhere else.
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: TI Process Calculation - Catch All Rule?

Post by declanr »

Struggling a little bit to follow exactly, but can you share a bit about where this code is in the TI process?

One theory is that if your code is on the data tab and being triggered more than once AND if "Other" is a child of "Control Total" - then on its first run it could put in the 0.05 against "other" to bring the "Control Total" up from 0.95 to 1.
But then on the second pass, the "Control Total" is now 1, so it does 1 - 1... which should be zero (but in TM1's magical binary floating point blah blah blah world it can actually be -0.0000000000000000000001 or something like that) and is then putting that against "Other" which brings "Control Total" back down to roughly 0.95.

If you have transaction logging on you can quickly narrow it down to see how many times that cell is updated while the TI runs.
If the concept I have described above is the case, you just need to make sure that this step of the process is moved out to a step of its own at the end after all other data is loaded and it will only pass over each relevant cell once. You can do this with while loops on the epilog or a TI of its own.
Declan Rodger
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: TI Process Calculation - Catch All Rule?

Post by michaelc99 »

declanr wrote: Mon Apr 18, 2022 10:22 pm Struggling a little bit to follow exactly, but can you share a bit about where this code is in the TI process?

One theory is that if your code is on the data tab and being triggered more than once AND if "Other" is a child of "Control Total" - then on its first run it could put in the 0.05 against "other" to bring the "Control Total" up from 0.95 to 1.
But then on the second pass, the "Control Total" is now 1, so it does 1 - 1... which should be zero (but in TM1's magical binary floating point blah blah blah world it can actually be -0.0000000000000000000001 or something like that) and is then putting that against "Other" which brings "Control Total" back down to roughly 0.95.

If you have transaction logging on you can quickly narrow it down to see how many times that cell is updated while the TI runs.
If the concept I have described above is the case, you just need to make sure that this step of the process is moved out to a step of its own at the end after all other data is loaded and it will only pass over each relevant cell once. You can do this with while loops on the epilog or a TI of its own.
Hello Declanr,

Sure thing. I created the TI Process using a cube view, but I set nearly all of the variables to ignore except for the period and prod center dimensions. My initial thought process there was that I needed to load them to be able to use them for evaluation and running calculations. However, I can (and should?) set the data source to none, if required. Additionally, "Other" is a child member of "Control Total". Currently, there is no code in Prolog, MetaData or Epilog. The aforementioned code is at the bottom of the "Data" section. I initially included it in the WHILE statement, but moved it at the bottom of the "Data" section for fear that it might be running multiple times.

However, in thinking about what you wrote I'm starting to wonder if having a data source defined is partially the root cause here. Earlier, when I used ASCIIOUTPUT on specific variables earlier I saw over 39,000 records where I would expect 11, at most, to cover the product lines. So, I bet the WHILE loop is running for all 39K+ records

I will enable transaction logging to confirm.

Thank you!
Michael
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: TI Process Calculation - Catch All Rule?

Post by michaelc99 »

Hello Declanr,

With your theory in mind, I reviewed the process again and it looks like I was indeed running the "Other" category twice. The initial run was as a subset that ran during the WHILE clause and the second run was during the "IF(category=OTHER) then 1 minus OTHERPCT" section of the code. Once I removed OTHER from the subset, the process ran successfully.

Thank you!
Michael
Post Reply