A Third Approach: Using DIMIX for Comparisons

This next example replaces all the calculation statements in the rule for the Plan cube with a single conditional statement that calculates allPlanned Production Qty - Kgs values, whether actual or projected.

Because the months are arranged in sequential order in the Month dimension, the index values for the earlier months of the year are smaller than the index values for later months. You can use this fact to your advantage in constructing a statement that pulls values for all months before March from the Production cube, while calculating values for March and later months directly in the Plan cube.

['Planned Production Qty - Kgs']=IF(DIMIX('Month',!Month)<DIMIX('Month',
'March'),DB('Production',!CakeType,!Month,'Quantity
Produced - Kgs'),DB('Plan',!CakeType,DIMNM('Month',(DIMIX('Month',
!Month)-1)),'Planned Production Qty - Kgs')*['Adj. % from last month']);

This single statement accurately retrieves and calculates values for all months. If the value requested is for a month prior to March, the function DB('Production',!CakeType,!Month,'Quantity Produced - Kgs') retrieves the value from the Production cube. If the value requested is for the month of March or later, the formula DB('Plan',!CakeType,DIMNM('Month',(DIMIX('Month',!Month) -1) ),'Planned Production Qty - Kgs')* ['Adj. % from last month'] calculates the value.

This single statement is an improvement over previous solutions, but it uses a literal month name as an argument to the second DIMIX function. You must edit this month name as you progress from month to month.

Ideally, though, you should not need to edit a rules statement on a monthly basis.

One possible solution would be to use the TIMVL function, which returns the numeric value of a component of a date/time value, in combination with the NOW function, which returns the date/time value of the current time. For example, TIMVL(NOW,'M') returns the numeric value of the month for the current time. Any time during the month of March, this function returns 3. This numeric value nicely coincides with the index value for the March element in the Month dimension. For more information on all rules functions, see TM1 Reference.

You can now fine-tune the single calculation statement in the rule for the Plan cube so that values for all months before the current month are pulled from the Production cube, while values for the current month and later are calculated in the Plan cube.

['Planned Production Qty - Kgs']=IF(DIMIX('Month',!Month)<TIMVL(NOW,'M'),
DB('Production',!CakeType,!Month,'Quantity
Produced - Kgs'),DB('Plan',!CakeType,DIMNM('Month',(DIMIX('Month',!Month)-1)),
'Planned Production Quantity - Kgs')*[Adj. % from last month']);

Using the TIMVL function eliminates the monthly rule change requirement. This type of solution is appropriate where you are interested in moment-by-moment time changes, for example in stock market applications.

For a monthly budgeting application, though, this solution is less than ideal because you might want to control the month for which data is queried. After all, most companies do not have all their actuals ready on the first of the month. Also, you might want to set the month back later, to review projections as they appeared on a previous month.