Calculations Types

Best Practices

As a best practice, always use the drop-down to select the calculation type. If you use the drop-down in the Type column, then the Name drop-down will be populated for you. If you simply type the calculation type, then the Name drop-down is not populated.

Note:

For the second and following rows of the calculation, if there is no drop-down in column A, then copy the drop-down from the first row, column A in the Type section to the rows beneath.

Aggregation

Aggregation is performed for the entire model unless a Scope is defined. Aggregation Type is case-sensitive.

AppendMetadata

Allows you to add new members to an existing dimension. Until this release, appending metadata was only available from the user interface (Model, Dimension, Append actions).

Appending metadata differs from a full or partial Model Refresh. For example, let’s say you are loading data from Planful Structured Planning, Consolidation, or Reporting applications and you want to refresh that data every month. There is no need to clear and recreate the entire model, which is what a full refresh would do. If you perform a partial refresh, only the mapping of type Data is performed meaning that new a dimension member (like a new Department) is not mapped so the Calculation will fail due to the new member. 

This feature also differs from the Append action you can select for a dimension because selecting Append allows you to enter new dimension members directly into Dynamic Planning, but does not load them from another source like Planful Structured Planning, Consolidation, or Reporting applications.

How to Use Append Metadata in a Calculation?

1. In SpotlightXL, navigate to the Model task and the Calculation subtask.
2. Select the model in which you want to append metadata.
3. Under Type, select AppendMetadata as shown below.
   
   
   

An example of typical full refresh calculation steps is shown below. Notice the full refresh clears the model and generates it again while the partial refresh appends the metadata.

Breakback

You can run one or more Breakback operations from a Calculation, thus automating your top-down allocations and making them easier to maintain. Use SpotlightXL to define Breakback operations in a calculation. Once defined, you can run the calculation from SpotlightXL or Spotlight.

Breakback requires several pieces of information:

• Model name
• Parent-level data intersection from which to allocate data to children
• Spread method
• Type of change to make: amount or percentage increase/decrease
• Value of the change to make
• Data intersection to use as a reference for the Breakback (optional)
• Hold members (optional, child members that will be skipped in the Breakback)

You can provide the required information using the Breakback button on the Calculation ribbon.

This button functions similarly to the Breakback button in Views and Reports. The main difference is that you must also specify the parent-level data intersection; in views and reports, this is the data cell that your cursor is on before you select Breakback.

For examples of how to set up Breakback in a calculation with a Value Input or Value Reference, see Running Breakback from a Calculation.

ClearAllData

Removes all data within a cleared model.

ClearExternalSource

Clears all the data in an External Source Model (ESM).

ClearLeafData

Clears all leaf level data. ClearLeafData is different from ClearRollupData and ClearAllData in that ClearLeafData action does not remove data. ClearLeafData updates leaf data with the value 0 (zero).

ClearModel

Returns a model to a state of "Not Generated" where it can be deleted.

ClearRollupData

Removes all roll-up data, not leaf level data.

DownloadData

Downloads data from Planful Structured Planning, Consolidation, or Reporting applications to SpotlightXL based on the maps defined for the model. Select the Manage task and the Application Administration - Request Status subtask to view download results.

As a best practice, we recommend downloading metadata and data using a Calculation with the option to run in the background set to Yes.

All setup must be complete before downloading data. For example, you need to define a model and target dimensions. At least one Power user must be defined before data can be downloaded.

Data Download will download only data based on the union of all “Data” type maps that are defined for the model. This is done to ensure that the process is efficient and no extraneous data is downloaded in the process.

Before data is downloaded from PCR, the system checks to see when the partitions in PCR were last processed. If there was any change in the PCR partitions since the last data download, the system downloads only the changes. This optimization speeds up the data download process.

DownloadMetadata 

Downloads all metadata from Planful Structured Planning, Consolidation, or Reporting applications to SpotlightXL. 

ExternalSourceFormula 

Runs all formulas associated with an External Source Model. This option is typically used with models that use a Lookup table for a set of drivers or user-inputted weights. For example, define a field in the Source Model for Sales drivers by Region where the drivers are provided in a lookup table for each region. You can run the formulas for the sales drivers in the Source Model by specifying ExternalSourceFormula and the name of the Source Model. 

ExternalSourceMap 

Copies data from an External Source Model to a Master Model. The External Source Map must be of the type Data. Mappings are not case-sensitive.

See: How to Load an External Data Source into a Master Model (Legacy) or How to Create and Load External Source Models (New).

Formula

A Formula is a mathematical expression to calculate a value within a single dimension. Formula type and name are case sensitive.

GenerateModel

Generates a model that is in a "Not Generated" state.

LockModel / UnlockModel

Select the LockModel or UnlockModel type as a line item in a Calculation. Performing the lock or unlock in a calculation saves time as it automates the process. For example, you might want to unlock a locked model, generate a formula, run aggregation, and then lock the model once again. If this is the case, your Calculation might look like the following:

When the Calculation runs, it checks whether the model is locked or not before executing each line item/step. If the model is locked, the calculation will be terminated and you will be notified.

Map

Map is applicable when the calculation results from a mapping (similar to reference account mapping in Planful). Map Type and Name cells are case sensitive.

Name Options Available for Each Calculation Type

For each calculation Type, the Name column has a drop-down of available options. In the Type column, you must use the drop-down to select the calculation step you want to run, and then the Name drop-down will be populated for you. If you simply type the calculation type, then the Name drop-down is not populated.