TM1 Forum

Posted: **Mon Feb 06, 2017 10:08 am**

Hi all,

I'm trying to create a running total.

My issue is that if a particular week has zero payment, the calculation is ignored, so I get no value in the running total.

What I would like is for the calculation to occur only if there is a movement that week or if the previous week's running total is non-zero.

I'm aware that ViewExtractSkipZeroesSet will force the process the run for all values, but then the computation time makes the process unusable.

Is there some way to run the data tab dependent on multiple inputs?

Code: Select all

#This line from prolog tab for context
vPriorWeek=ATTRS('TaxWeek',pTaxWeek,'Prior Tax Week');

#Process should only run for one week at a time. To add to view in prolog tab after troubleshooting
IF (TaxWeek@<>pTaxWeek);
ITEMSKIP;
ENDIF;



#Previous balance
PrevBal=CellGetN('CumulativeCube',vPriorWeek,Contractor,'Holiday Cumulative');

#This Week's movement

HolidayCalcPaid=CellGetN('MainCube','Actual','DataSource',Pay_Frequency,Scheme,Account_Manager,BDM,Agency,IC,PC,Contractor,TaxWeek,'Holiday Paid');
HolidayCalcRetained=CellGetN('MainCube','Actual','DataSource',Pay_Frequency,Scheme,Account_Manager,BDM,Agency,IC,PC,Contractor,TaxWeek,'Holiday Retained');
Movt=HolidayCalcRetained-HolidayCalcPaid;

#Attempt to 
IF(Movt=0 & PrevBal=0);
  ITEMSKIP;
  ENDIF; 

CellPutN(Movt,'CumulativeCube',TaxWeek,Contractor,'Holiday Movt');

CurrentBal=PrevBal + Movt;
CellPutN(CurrentBal,'CumulativeCube',TaxWeek,Contractor,'Holiday Cumulative');


#Filling Main with same cumulative value

#CellPutN(CurrentBal,'Maincube','Actual',vDataSource,Pay_Frequency,Scheme,Account_Manager,BDM,Agency,IC,PC,Contractor,TaxWeek,'Holiday Cumulative');

Posted: **Mon Feb 06, 2017 11:20 am**

Shouldn't your data source be the sum of the running total and the weekly value? Then you will cover all the intersections that you need to?

Posted: **Wed Feb 08, 2017 4:46 pm**

Steve Rowe wrote:Shouldn't your data source be the sum of the running total and the weekly value? Then you will cover all the intersections that you need to?

I failed at getting the record to feed from data source, and instead used rules rather than a TI process for my accumulation.
I summarised the list of dimensions for readability- code included below for future reference.

Code: Select all

#Filling first week
['36/2016','Opening Holiday Cumulative']=N:['35/2016','Holiday Cumulative'];
#Filling Future Weeks
['Opening Holiday Cumulative']=N:DB('Cubename','DIMENSIONS IN CUBE',ATTRS('Tax_Week',!Tax_Week,'Prior Tax Week'),'Holiday Cumulative');

FEEDERS;
['35/2016','Holiday Cumulative']=>['36/2016','Opening Holiday Cumulative'];
['Holiday Cumulative']=>DB('Cubename',!DIMENSIONS IN CUBE,ATTRS('Tax_Week',!Tax_Week,'Next Tax Week'),'Opening Holiday Cumulative');

TM1 Forum

Forcing Calculation on zeros if another variable is non-zero

Forcing Calculation on zeros if another variable is non-zero

Re: Forcing Calculation on zeros if another variable is non-zero

Re: Forcing Calculation on zeros if another variable is non-zero