TM1 Forum

Currently on Win XP, TM1 10.1, and Excel 2007

I am constructing an Initiatives Cube for a forecast model that is in development. As you can see from the print screen the manual spread method works just fine. It takes the Amount Change for Period Input divided by the (End Month minus the Start Month). Here is the tricky part. I also have a Spread Method cube built as shown on the second print screen on the attached file. What I am trying to accomplish is that if 4-4-5 is selected under Total Basic Equip that Nov thru Mar it would proportionally spread over these period according to the Spread Method Cube.

Question:What would be the proper formula to go after the Spread Method Cube and sum the 'Amount' under the 4-4-5 method from the Start and End Months selected in the Initiatives cube?

Here is the current code i wrote for that Manual Spread Method.
['Active','Amount Change' {'P1','P2','P3','P4','P5','P6','P7','P8','P9','C1','C2','C3','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','3
6'}]=N:IF(DB('Patt_Initiatives', !Patt_Companies, !Patt_Version, !Patt_Initiatives, !Patt_Initiatives_Adjustments, !Patt_Forecast_Summary_Accounts, 'Spread Method')@='Manual',IF(
ATTRN('Patt_Period',ATTRS('Patt_Rolling_Periods',DB('Patt_Initiatives', !Patt_Companies, !Patt_Version, !Patt_Initiatives, !Patt_Initiatives_Adjustments, !Patt_Forecast_Summary_Accounts, 'Start Month'),'Description'),'Fiscal Number')
<=
ATTRN('Patt_Period',ATTRS('Patt_Rolling_Periods',!Patt_Rolling_Periods,'Description'),'Fiscal Number')
&
ATTRN('Patt_Period',ATTRS('Patt_Rolling_Periods',DB('Patt_Initiatives', !Patt_Companies, !Patt_Version, !Patt_Initiatives, !Patt_Initiatives_Adjustments, !Patt_Forecast_Summary_Accounts, 'End Month'),'Description'),'Fiscal Number')
>=
ATTRN('Patt_Period',ATTRS('Patt_Rolling_Periods',!Patt_Rolling_Periods,'Description'),'Fiscal Number')
,
['Period Input','Amount Change']
\
(ATTRN('Patt_Period',ATTRS('Patt_Rolling_Periods',DB('Patt_Initiatives', !Patt_Companies, !Patt_Version, !Patt_Initiatives, !Patt_Initiatives_Adjustments, !Patt_Forecast_Summary_Accounts, 'End Month'),'Description'),'Fiscal Number')+1
-
ATTRN('Patt_Period',ATTRS('Patt_Rolling_Periods',DB('Patt_Initiatives', !Patt_Companies, !Patt_Version, !Patt_Initiatives, !Patt_Initiatives_Adjustments, !Patt_Forecast_Summary_Accounts, 'Start Month'),'Description'),'Fiscal Number'))
,CONTINUE),CONTINUE);


I have started to work on the 4-4-5 formula but cant seem to get the formula correct. 4-4-5 Formula is listed below. I need to find a way to go after the Spread Method cube for the specific months listed as Start and End then Sum the Amount for this range.
['Active','Amount Change',{'P1','P2','P3','P4','P5','P6','P7','P8','P9','C1','C2','C3','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','3
6'}]=N:IF(DB('Patt_Initiatives', !Patt_Companies, !Patt_Version, !Patt_Initiatives, !Patt_Initiatives_Adjustments, !Patt_Forecast_Summary_Accounts, 'Spread Method')@='4-4-5',IF(
ATTRN('Patt_Period',ATTRS('Patt_Rolling_Periods',DB('Patt_Initiatives', !Patt_Companies, !Patt_Version, !Patt_Initiatives, !Patt_Initiatives_Adjustments, !Patt_Forecast_Summary_Accounts, 'Start Month'),'Description'),'Fiscal Number')
<=
ATTRN('Patt_Period',ATTRS('Patt_Rolling_Periods',!Patt_Rolling_Periods,'Description'),'Fiscal Number')
&
ATTRN('Patt_Period',ATTRS('Patt_Rolling_Periods',DB('Patt_Initiatives', !Patt_Companies, !Patt_Version, !Patt_Initiatives, !Patt_Initiatives_Adjustments, !Patt_Forecast_Summary_Accounts, 'End Month'),'Description'),'Fiscal Number')
>=
ATTRN('Patt_Period',ATTRS('Patt_Rolling_Periods',!Patt_Rolling_Periods,'Description'),'Fiscal Number')
,
(['Period Input','Amount Change']
\
(ATTRN('Patt_Period',ATTRS('Patt_Rolling_Periods',DB('Patt_Initiatives', !Patt_Companies, !Patt_Version, !Patt_Initiatives, !Patt_Initiatives_Adjustments, !Patt_Forecast_Summary_Accounts, 'End Month'),'Description'),'Fiscal Number')+1
-
ATTRN('Patt_Period',ATTRS('Patt_Rolling_Periods',DB('Patt_Initiatives', !Patt_Companies, !Patt_Version, !Patt_Initiatives, !Patt_Initiatives_Adjustments, !Patt_Forecast_Summary_Accounts, 'Start Month'),'Description'),'Fiscal Number')))
*
DB('Patt_Spread Method', ATTRS('Patt_Rolling_Periods',!Patt_Rolling_Periods,'Description'),
DB('Patt_Initiatives', !Patt_Companies, !Patt_Version, !Patt_Initiatives, 'Amount Change', !Patt_Forecast_Summary_Accounts, 'Spread Method'), 'Amount')
,CONTINUE),CONTINUE);

I think you might be trying to do this the hard way. For this type of spread calculation I always use a seasonality profile cube that holds the monthly or weekly percentage splits for each method. Then all you need to do is use the selected spread allocation to look up the percentage allocation and multiply by the input value to be spread. It separates the logic and makes the calculation much more efficient as well as more understandable.

Trying to do the whole calculation in one rule is asking for trouble.

I agree this is a complicated way. As far as the percentage split is concerned. How would that work for a 4-4-5 split if the user can ideantify Start and End Month. The denominator is a moving target. For Example: If Nov has 4 weeks, Dec had 4 weeks, Jan has 5 weeks and Feb has 4 weeks and so on. If the user selects three months the denominator would be 13 but if they select four months the denominator would be 17. This would make my percentage variable for any given month depending on the length of period selected. So for Nov which has 4 week if they select three months to spread it would be .3076 and if they selected 4 months it would be .2352. I could be overthinging this big time so let me know if I am not understanding your simpler solution.