TM1 Forum

HI ,

I am writing a rule to calculate AverageFYTD Rate . And this is based on CurrentMonth .

So if current month is jul2019 , it takes june2018 to jun2019 rates and divides by 13 .
if current month is Aug 2019 , it takes jun 2019 ,jul2019 and divides by 2 .
This goes similarly until Jun 2020.

Sample rule looks like below .

['AverageCFYTDRate','Ending Rate']=
N:if(Month(Date((DayNo(Today(1))),1))=7,(DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'01')+ DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'02')+ DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'03')+ DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'04')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'05')+ DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'06')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'07')+ DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'08')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'09')+ DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'10')+ DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'11')+ DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'12')+ DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))-1,4,0)|'12'))/13,if(Month(Date((DayNo(Today(1))),1))=8,(DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'12')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'01'))/2,if(Month(Date((DayNo(Today(1))),1))=9,
(DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'12')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'01')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'02'))/3,if(Month(Date((DayNo(Today(1))),1))=10,(DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'12')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'01')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'02')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'03'))/4,if(Month(Date((DayNo(Today(1))),1))=11,(DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'12')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'01')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'02')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'03')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'04'))/5,if(Month(Date((DayNo(Today(1))),1))=12,(DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'12')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'01')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'02')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'03')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'04')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))+1,4,0)|'05'))/6,if(Month(Date((DayNo(Today(1))),1))=1,(DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1))-1,4,0)|'12')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'01')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'02')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'03')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'04')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'05')+DB('Exchange Rates','Actual',!Currencies,'USD','Ending Rate',STR(Year(Date((DayNo(Today(1))),1)),4,0)|'06'))/7.



Is there a better way to write this . Rule works fine and only one if is executed at a time , but the repeated code looks very odd.

I am always taking the values of completed month , when comparing a month in if.

If it works, it works...

But I would tend to be in favour of custom rollups in the last dimension of the Exchange Rates cube.
If needed, use Turbo Integrator to change (once a month) the components of the rollup and if it will greatly simplify the rule (at the expense of a TI process that you have to write but that will not be terribly difficult).
You can use custom weightings on the components of the rollup to mimick the factor 1/x.

This would appear to be a near perfect example of how (for a linear time dimension) custom consolidations with weightings would be a much more efficient design choice for such calculations.

Indeed if my cube didn't have a linear time dimension I would go so far as to have a helper calculation cube on the side just for the purposes to do calculations via rollups not rule as it will be that much more efficient and faster.