Constrained Calculations

You can use Constrained Calculations to narrow the scope of the recalculations. For information on how to enable Constrained Calculations, see Manually enabling features in the tm1features.json file.

Why does my data get recalculated?

Normally, when you refresh your worksheet, IBM® Planning Analytics for Microsoft Excel recalculates all data that is flagged as "dirty" in your workbook. Planning Analytics for Microsoft Excel recalculates this data in an attempt to ensure maximum data coherency.

Maximum data coherency

When you refresh a worksheet, Planning Analytics for Microsoft Excel will dirty the requested sheet, and then recalculate any dirty data in your workbook. This is in an attempt to ensure maximum data coherency at rest.

If you have other content in your workbook that is flagged as dirty, ensuring maximum data coherency might have a performance cost, as any dirty content has to be recalculated.

For more information on data coherency and dirty data, see Data coherency.

What does Constrained Calculations do?

You can use Constrained Calculations to limit the recalculations strictly to the specified worksheet. Because Planning Analytics for Microsoft Excel is recalculating only the single worksheet, you might notice improved performance when refreshing a single worksheet. Other content in your workbook that is flagged as dirty will not be recalculated if you use Constrained Calculations.

Why do the cells show RECALC_###?

Cells with unfinished calculations might display RECALC_###. Complete the refresh of any worksheets that contain RECALC_### to update those cells with data.

If your Calculation Options is set to Manual, then RECALC_### is the expected response for any triggered formula-based report content until a refresh gesture is performed.

If your Calculation Options is set to Automatic and you use Constrained Calculations, RECALC_### might occur if there are any other sheets that are invalidated beyond the sheet being refreshed. This can happen due to formula references to that sheet.

Important: Constrained Calculations in Automatic mode is supported only when r41_EnableConstrainedCalcScope is enabled. Deprecation of r41_EnableConstrainedCalcScope is targeted for a future release of Planning Analytics for Microsoft Excel. We recommend using r56_EnableManualConstrainedCalc instead to enable Constrained Calculations.

Which gestures prompt a calculation with Constrained Calculations enabled?

With Constrained Calculations enabled, gestures such as Refresh Sheet and Rebuild Sheet will prompt a calculation. For a full list of gestures that prompt a calculation with Constrained Calculations enabled, see Actions that prompt a Constrained Calculation.

Usage Notes

If your Calculation Options is set to Manual, it is acceptable to have shared report drivers referenced directly, since constrained manual calculation fully limits all costs.

If your Calculation Options is set to Automatic, it is suggested to use decoupled drivers, with Action Buttons to propagate driver information across the workbook instead of direct references. This is because under automatic recalculations, Microsoft Excel will still attempt to do the initial processing of any dirty content. Constrained Calculation will not change Excel behavior.

Important: Constrained Calculations in Automatic mode is supported only when r41_EnableConstrainedCalcScope is enabled. Deprecation of r41_EnableConstrainedCalcScope is targeted for a future release of Planning Analytics for Microsoft Excel. We recommend using r56_EnableManualConstrainedCalc instead to enable Constrained Calculations.

As of Planning Analytics for Microsoft Excel version 2.0.41, the benefits of Constrained Calculations are most noticeable if your worksheet does not contain volatile functions or persistently dirty data. If the worksheet that you are restricting the recalculation scope to contains volatile functions or persistently dirty data, Planning Analytics for Microsoft Excel might need to recalculate any sheets that it shares dependencies with.