TM1 IRR Calc

Post Reply
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

TM1 IRR Calc

Post by paulsimon »

Hi

Someone asked about doing an IRR (Internal Rate of Return) calc in TM1 on a Linked In forum. I answered it there, so I thought I would re-post it here in case it is of any use to anyone else.

This can be done in TM1 Rules or alternatively using an iterative WHILE loop in TM1 Turbo Integrator.

Whether it is worth the effort compared to using built in functions in Excel and storing the result in TM1 is debatable.

The following are the rules that prove it - sorry no feeders.

The convergence algorithm is noddy and there are better ones on the web

[img]
IRR.jpg
IRR.jpg (197.05 KiB) Viewed 11408 times
[/img]

Code: Select all

['IRR','Figures']=['IRR','50'] - 1;

['Figures']=N:STET ;

['Tolerance']=N:['Tolerance','Figures'];

['Year Num']=N: dimix( 'zTD_IRR_Year', !zTD_IRR_Year ) 
                          - dimix( 'zTD_IRR_Year', 'Y_All IRR Years' );


# Initial guess at IRR

['IRR','01']=N: ( ['Y_All IRR Years','Figures'] \ ABS( ['Y_2012','Figures'] )  ) ;

# Basic second change

['IRR Change','02']=N: 
            IF( ['Y_All IRR Years','01'] > 0 
                        ,
                        ['IRR Change','Figures']
                        ,
                        -1 * ['IRR Change','Figures']
             ) ;

# Later Changes until Tolerance reached
                            
['IRR Change' ]=N: 
    IF( abs( DB('zTD_IRR','Y_All IRR Years',
                         dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 )) )
            < ['Tolerance','Figures']
           ,
           0
           ,
           DB( 'zTD_IRR','IRR Change',
                                dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 ) 
            )
            *
           IF( DB('zTD_IRR','Y_All IRR Years',
                         dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 ))
                       > 0 
                        ,
                        IF( DB('zTD_IRR','Y_All IRR Years',
                                   dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 2 ))
                                   > 0 
                              ,
                              1.1
                              ,
                              -0.1
                        )
                        ,
                        IF( DB('zTD_IRR','Y_All IRR Years',
                                   dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 2 ))
                                   > 0 
                              ,
                              -0.1
                              ,
                              1.1
                         )
             ) 
    ) ;
                        
['IRR']=N: DB('zTD_IRR','IRR',
                         dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 ) 
                  ) 
                 + ['IRR Change'] ;


['IRR Reciprocal']=N:1\['IRR'];


[]=N:['Figures'] * ( ['IRR Reciprocal'] ^ ['Year Num']) ;

Here is the zTD_IRR_Year dimension

IRR
IRR Change
IRR Reciprocal
Tolerance
Y_All IRR Years
Y_2012
Y_2013
Y_2014
Y_2015
Y_2016

Here is the zTD_IRR_Iteration dimension

Figures
Year Num
All Iterations
01
02
03
...
50

You enter what you want in the Figures column. It does the iterations and returns the result in the [IRR , Figures] combination.

I tested it with an initial IRR Change of 0.1 and a Tolerance of 1 and the following figures in Y_2012 to Y_2016

-5000, 2000, 300, 4999, 4444

and the result was pretty close to the Excel IRR.

Regards

Paul Simon
User avatar
Harvey
Community Contributor
Posts: 236
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Re: TM1 IRR Calc

Post by Harvey »

This should probably find it's home in the "Useful Code, Tips and Tricks" forum...
Take your TM1 experience to the next level - TM1Innovators.net
kidyee
Posts: 11
Joined: Mon Jan 05, 2015 2:16 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 IRR Calc

Post by kidyee »

Hi paulsimon

Your IRR calc is excellent.!
I have used IRR on Rule reference by your posting

IRR cube basically too big. so It is necessity to the use of SKIPCHECK & FEEDERS in rule.
but.I'm thoroughly confused to the use of SKIPCHECK & FEEDERS

Especially like this Statement difficult for me.

Code: Select all

['IRR Change' ]= 
    N:IF( abs( DB('zTD_IRR','Y_All IRR Years', dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 )) ) < ['Tolerance','Figures']
           ,0
           ,DB( 'zTD_IRR','IRR Change', dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 ) )
            * IF( DB('zTD_IRR','Y_All IRR Years', dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 )) > 0 
                , IF( DB('zTD_IRR','Y_All IRR Years', dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 2 )) > 0 
                    , 1.1
                    ,-0.1 )
                ,IF( DB('zTD_IRR','Y_All IRR Years', dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 2 )) > 0 
                    , -0.1
                    ,1.1 )
             ) 
    ) ;

I need help.

I'm not good English.
understands me.
thanks for read my word.
BariAbdul
Regular Participant
Posts: 424
Joined: Sat Mar 10, 2012 1:03 pm
OLAP Product: IBM TM1, Planning Analytics, P
Version: PAW 2.0.8
Excel Version: 2019

Re: TM1 IRR Calc

Post by BariAbdul »

IRR cube basically too big. so It is necessity to the use of SKIPCHECK & FEEDERS in rule.
but.I'm thoroughly confused to the use of SKIPCHECK & FEEDERS
As far as I understand,SkipCheck and feeders are not needed:
1.Cube is highly dense
2. Cube contains only strings.
So,Check the sparsity of your cube.You can estimate density by answering this question: If one element in the dimension has a value, keeping the elements of the other dimensions constant, what is the probability that the other elements in the dimension have a value? Thanks
"You Never Fail Until You Stop Trying......"
kidyee
Posts: 11
Joined: Mon Jan 05, 2015 2:16 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 IRR Calc

Post by kidyee »

I just need to FEEDERS.
So. I try many time.
that result is like this

Code: Select all

SKIPCHECK;
['IRR','Figures']=['IRR','50'] - 1;
['Figures']=N:STET ;
['Tolerance']=N:['Tolerance','Figures'];
['Year Num']=N: dimix( 'zTD_IRR_Year', !zTD_IRR_Year ) - dimix( 'zTD_IRR_Year', 'Y_All IRR Years' );


# Initial guess at IRR
['IRR','01']=N: ( ['Y_All IRR Years','Figures'] \ ABS( ['Y_2012','Figures'] )  ) ;

# Basic second change
['IRR Change','02']=
	N: IF( ['Y_All IRR Years','01'] > 0
        , ['IRR Change','Figures']
        , -1 * ['IRR Change','Figures']
	) ;

# Later Changes until Tolerance reached
['IRR Change' ]=
	N:IF( abs( DB('zTD_IRR','Y_All IRR Years', dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 )) ) < ['Tolerance','Figures']
		, 0
		, DB( 'zTD_IRR','IRR Change', dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 ) )
           *IF( DB('zTD_IRR','Y_All IRR Years', dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 )) > 0
                , IF( DB('zTD_IRR','Y_All IRR Years', dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 2 )) > 0
                    , 1.1
					, -0.1
				)
				, IF( DB('zTD_IRR','Y_All IRR Years', dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 2 )) > 0
					, -0.1
					, 1.1
				)
			)
	) ;

['IRR']=N: DB('zTD_IRR','IRR', dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 ) ) + ['IRR Change'] ;
['IRR Reciprocal']=N:1\['IRR'];

[]=N:['Figures'] * ( ['IRR Reciprocal'] ^ ['Year Num']) ;

FEEDERS;
['50'] => ['Figures'];
['Figures'] => ['Tolerance'];
['Y_All IRR Years'] => ['Year Num'];
['01'] => ['IRR Change'];

['Y_All IRR Years'] => ['All Iterations'];
['Figures'] => ['02'];

['Y_All IRR Years'] => ['IRR Change','Year Num' ];
['IRR Change'] => ['IRR'];
['All Iterations'] => ['IRR'];
['IRR'] => ['IRR Reciprocal'];
['Figures'] => DB('zTD_IRR', !zTD_IRR_Year, !zTD_IRR_Iteration);
But I'm not sure If my coding is rights or wrong.
※ once running well.

I'm need recommend to my feeders coding.

thanks.
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: TM1 IRR Calc

Post by Steve Rowe »

Hi Kidyee,

If your example is the same as Pauls then feeders are not required since the cube is 100% dense, i.e. every cell is populated with numbers. In this situation skipcheck / feeders don't help as there is no blank space to skip.

If it is not like Pauls example then you'll need to share some detail but I would say that almost definitely the feeder would be the value in the equivalent position to the figures column in Pauls example.

Cheers,
Technical Director
www.infocat.co.uk
kidyee
Posts: 11
Joined: Mon Jan 05, 2015 2:16 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 IRR Calc

Post by kidyee »

Steve Rowe wrote:Hi Kidyee,

If your example is the same as Pauls then feeders are not required since the cube is 100% dense, i.e. every cell is populated with numbers. In this situation skipcheck / feeders don't help as there is no blank space to skip.

If it is not like Pauls example then you'll need to share some detail but I would say that almost definitely the feeder would be the value in the equivalent position to the figures column in Pauls example.

Cheers,
Good to see you Steve Rowe
according to your opinion, 100% dense cube is not required skipcheck& feeders right?
I make sense new. thanks.

so, I have question.
Q1. how % cube have needed skipchek & feeders
Q2. charactor data need not skipckek & feeders? right?
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: TM1 IRR Calc

Post by Steve Rowe »

Hi,

Q1. I'm not going to attempt to put an exact % on this, it really depends on the number of dimensions in the cube and how hard the rules are working. Bottom line is the more sparse your data is and the harder the rules are working the greater the benefit of feeding the rules.

Q2. The decision to feed or not feed is the same irrespective of strings or data. Often text data is close to 100% dense and so may not need feeding.

Cheers
Technical Director
www.infocat.co.uk
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: TM1 IRR Calc

Post by paulsimon »

Hi Kidyee

It was a long time ago since I wrote that IRR code. It was only ever intended to show that an IRR calc could be done in TM1. I did mention that it might be more efficient in TI or to just use Excel's IRR formula and DBS in the value.

However, I am curious as to why you are saying that it is using to much memory. The example I produced was tiny.

Are you perhaps not showing the real code that you are using? Are you perhaps trying to apply the IRR calc for a thousand cost centres perhaps combined with some other splits?

In that case you will need feeders. I would suggest that you keep the IRR area fully fed, but only feed if there are values entered against the cost centre.

Regards

Paul Simon
User avatar
Oratia623
Posts: 40
Joined: Mon Apr 27, 2009 5:36 am
OLAP Product: TM1/PA/CA
Version: V7.x to 2.0.9+
Excel Version: All
Location: Sydney, Australia

Re: TM1 IRR Calc

Post by Oratia623 »

Hi,
As another option (and in my mind a superior one) we now use java to calculate our IRRs.
We trialed these rules and a cube/TI combination option and the java results are 100% accurate to excel, and blisteringly fast, compared to other alternatives
So, we now have a TI process that generates the cashflow from the source (cube), and then calls a "home-built" java function that uses an embedded opensource IRR java class to actually calculate the IRR. This is then returned to the TI to be written back to TM1. No need for rules, feeders or dedicated cubes.
From a standing start of knowing nothing about java, it took me about 2 (very hard) days to get there to a first working prototype.

Paul
Last edited by Oratia623 on Thu Jun 04, 2015 3:18 am, edited 1 time in total.
Paul Williamson
____________________________________________________________________________________
I came. I saw. I did not concur.
nick_leeson
Posts: 98
Joined: Sat Feb 11, 2012 11:13 am
OLAP Product: TM1 9x, BPC, Hyperion, HANA
Version: TM1 10
Excel Version: Excel 2003 - 2010

Re: TM1 IRR Calc

Post by nick_leeson »

Hi,
As another option (and in my mind a superior one) we now use java to calculate our IRRs.
We trialed these rule rules and a cube/TI combination option and the java results are 100% accurate to excel, and blisteringly fast, compared to other alternatives
So, we now have a TI process that generates the cashflow from the source (cube), and then calls a "home-built" java function that uses an embedded opensource IRR java class to actually calculate the IRR. This is then returned to the TI to be written back to TM1. No need for rules, feeders or dedicated cubes.
From a standing start of knowing nothing about java, it took me about 2 (very hard) days to get there to a first working prototype.
Thank you for the idea :D
kidyee
Posts: 11
Joined: Mon Jan 05, 2015 2:16 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 IRR Calc

Post by kidyee »

Hello paulsimon

I'm not sure if I understand your word or not. because I'm not in English Culture
anyway My Cube has 6 Dimensions
COM_Year has 10 elements('2011' ~ '2020')
COM_Month has 13 elements('Jan' ~ 'Dec' and 'Full_Year')
COM_Day has 32 elements ('01' ~ '31' and 'Full_Month')
COM_NoWithTotal 1008 elements ('1' ~ ' 1008') This Dimensions make One day tranjection for contract
TY_DailyIRR_Iteration 64 elements ('Figures', 'ZeroCNT', 'Payment Num', 'All Iterations', and '1st' ~ '59th')
TY_DailyIRR_Payment 97 elements ('Contract No.','Product_Type','Segment3','IRR','IRR Change','IRR Reciprocal','Tolerance','Financed Amount','Total IDC','Total Subsidy','All IRR Payment','Initial Cash Outflow' and 'Payment 1' ~ 'Payment 84' and 'Residual Value')

Here is my rule

Code: Select all

#SKIPCHECK;
# INITIAL SETTING FOR IRR
['IRR Change','Figures'] =
	N:IF(DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Iteration, 'Contract No.') @= ''
		,0
		,0.1
	);
['Tolerance' ,'Figures'] =
	N:IF(DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Iteration, 'Contract No.') @= ''
		,0
		,1
	);

['Initial Cash Outflow', 'Figures'] = N:['TotalSubsidy','Figures'] - ['Financed Amount','Figures'] - ['Total IDC','Figures'];
['IRR','Figures']=(['IRR','59th'] - 1)*12;
#######

['Figures' ] = N:DB('int_TY_Daily_IRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Payment);
['Figures' ] = S:DB('int_TY_Daily_IRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Payment);
#['Figures' ] = N:STET ;
['Tolerance'] = N:['Tolerance','Figures'];
['Product_type'] = ['Contract No.'];


#Payment period Calculation for IRR
['ZeroCNT'] = N:IF( ['Figures'] = 0, 1, 0);
['PayCNT'] =
	N:IF(dimix( 'TY_DailyIRR_Payment', !TY_DailyIRR_Payment ) - dimix( 'TY_DailyIRR_Payment', 'All IRR Payment' ) > 0
		,dimix( 'TY_DailyIRR_Payment', !TY_DailyIRR_Payment ) - dimix( 'TY_DailyIRR_Payment', 'All IRR Payment' )
		,0
	);
['Residual Value','Payment Num'] =
	N:IF( ['Figures','Residual Value'] = 0, 0, ['Residual Value','PayCNT']-['All IRR Payment','ZeroCNT']);

['Payment Num'] =
	N:IF(dimix( 'TY_DailyIRR_Payment', !TY_DailyIRR_Payment ) - dimix( 'TY_DailyIRR_Payment', 'All IRR Payment' ) > 0
		,IF( ['Figures'] = 0
			,0
			,dimix( 'TY_DailyIRR_Payment', !TY_DailyIRR_Payment ) - dimix( 'TY_DailyIRR_Payment', 'All IRR Payment' )
		)
		,0
	);

# Initial guess at IRR
['IRR','1st'] = N:ABS( ['All IRR Payment','Figures'] \ ['Initial Cash Outflow','Figures']  ) ;

# Basic second change
['IRR Change','2nd'] =
	IF( ['All IRR Payment','1st'] > 0
		,['IRR Change','Figures']
		,-1 * ['IRR Change','Figures']
	) ;

# Later Changes until Tolerance reached
['IRR Change'] =
	N:IF( ABS( DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, dimnm( 'TY_DailyIRR_Iteration', dimix( 'TY_DailyIRR_Iteration', !TY_DailyIRR_Iteration) - 1 ), 'All IRR Payment') ) < ['Tolerance','Figures']
	# N:IF( ABS( ['All IRR Payment']-1previous cell ) < ['Tolerance','Figures']
		,0
		,DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, dimnm( 'TY_DailyIRR_Iteration', dimix( 'TY_DailyIRR_Iteration', !TY_DailyIRR_Iteration) - 1 ), 'IRR Change')
		#['IRR Change']-1previous cell
		*IF( DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, dimnm( 'TY_DailyIRR_Iteration', dimix( 'TY_DailyIRR_Iteration', !TY_DailyIRR_Iteration) - 1 ), 'All IRR Payment') > 0
		#*IF(['All IRR Payment']-1previous cell > 0
			,IF( DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, dimnm( 'TY_DailyIRR_Iteration', dimix( 'TY_DailyIRR_Iteration', !TY_DailyIRR_Iteration) - 2 ), 'All IRR Payment') > 0
			#*IF(['All IRR Payment']-2previous cell > 0
				,1.1
				,-0.1
			)
			,IF( DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, dimnm( 'TY_DailyIRR_Iteration', dimix( 'TY_DailyIRR_Iteration', !TY_DailyIRR_Iteration) - 2 ), 'All IRR Payment') > 0
			#*IF(['All IRR Payment']-2previous cell > 0
				,-0.1
				,1.1
			)
		)
	);

['IRR'] = N:DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, dimnm( 'TY_DailyIRR_Iteration', dimix( 'TY_DailyIRR_Iteration', !TY_DailyIRR_Iteration) - 1 ), 'IRR') + ['IRR Change'] ;
['IRR Reciprocal']=N:1\['IRR'];
[]=N:['Figures'] * ( ['IRR Reciprocal'] ^ ['Payment Num']) ;

#FEEDERS;
# INITIAL SETTING FOR IRR
#['TotalSubsidy','Figures'] => ['Initial Cash Outflow', 'Figures'];
#['Financed Amount','Figures'] => ['Initial Cash Outflow', 'Figures'];
#['Total IDC','Figures'] => ['Initial Cash Outflow', 'Figures'];

#['IRR','59th'] => ['IRR','Figures'];
#######

#['Figures' ] = N:STET ;
#['Tolerance','Figures'] => ['Tolerance'];

#Payment period Calculation for IRR

#['All IRR Payment'] => ['PayCNT'];

#['Residual Value','PayCNT'] => ['Residual Value','Payment Num'];
#['All IRR Payment','ZeroCNT'] => ['Residual Value','Payment Num'];

#['All IRR Payment'] => ['Payment Num'];

# Initial guess at IRR
#['All IRR Payment','Figures'] => ['IRR','1st'];
#['All IRR Payment'] => ['All Iterations'];

# Basic second change
#['IRR Change','Figures'] => ['IRR Change','2nd'];

# Later Changes until Tolerance reached
#['IRR Change'] => ['IRR Change'];
#['All IRR Payment'] => ['IRR Change'];
#['IRR Change'] => ['IRR'];
#['IRR'] => ['IRR'];
#['IRR'] => ['IRR Reciprocal'];
#['Figures'] => DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Iteration, !TY_DailyIRR_Payment);
Image
Image
Image
Image
I hide contract no element for security

But I used skipcheck & feeders But Saved not (I'm about to push save button and not repones.)
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: TM1 IRR Calc

Post by Steve Rowe »

I'd suggest that you have a think about feeders and get straight in your head exactly what a feeder is and what its for.

You are simply telling the engine when the value is going to generate a non-zero result, generally it is the rule "backwards", but it doesn't have to be you can use whatever is convenient to flag the calc to take place. It also does not need to be 100% correct, you can over feed the values and providing you are happy with the calculation speed the job is done. (Under feeding is obviously bad as you get the wrong answer).

These two lines form the source of the calculations

['Figures' ] = N:DB('int_TY_Daily_IRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Payment);
['Figures' ] = S:DB('int_TY_Daily_IRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Payment);

The feeder for Figures needs to go in the rule sheet for int_TY_Daily_IRR cube, I don't think that's the rule sheet you have shown, not sure. I assume that is where you are picking up the starting conditions. All the stuff above All IRR Payment.

Then use one of the starting conditions Financed Amount, say to feed the consolidation All IRR Payment, I think that will do the job.

If that works and performs I'd leave it at that.

If it doesn't then you need to get clever and stop the feeder from firing when Payment XX is going to evaluate to 0, this is going to be much harder as you will probably need to repeat the maths in the feeder calc. Probably not be worth the effort since you only have 86 elements in the consolidation and I wouldn't worry about the fact that sometimes the Payment elements are over fed.

Put another way I'd regard each column in your view as either needing to be fed or not and then just feed it with the one thing that indicates that there is work to do.

What I would also look at is ensuring that payment calc evaluates to zero so that it does the minimum amount of work when it is overfed

An obvious problems

This is circular, not sure what would happen, don't think I've tried but it won't make the rule engine happy.
#['IRR'] => ['IRR'];

Hope this helps,

Steve
Technical Director
www.infocat.co.uk
Post Reply