Calculations

Overview

Create a Calculation, which identifies the order of execution for aggregations, formulas, maps, and more. Access the Calculation subtask by selecting the Model task and the Calculation subtask.

The calculations displayed for the Master model are those member formulas defined in Planful Structured Planning, Consolidation, or Reporting applications and carried over into SpotlightXL. The translated formulas in Planful Structured Planning, Consolidation, or Reporting applications will be carried over to SpotlightXL and executed in the Calculation.

Description of Ribbon Fields

A description of ribbon fields and actions is provided below.

Folders

Organize your Calculations based on the models from which they were created. Add Calculations to your Favorites folder by right-clicking on the Calculation and selecting Add to Favorites. In the example below, the Balance Sheet and Model Refresh Calculations are organized in the Balance Sheet folder.

Use the Search functionality at the top of the screen to search for Calculations, then use the up and down arrows to scroll through the results.

Favorites and Recent folders are user specific. The Recent folder displays 10 recently accessed Calculations by user across all Models. The Favorites folder displays 10 Calculations. If you add more than 10 items, the most recent 10 are displayed.

You can add folders, delete and rename folders as well as move Calculations from folder to folder by dragging and dropping.

How to Add a Folder

1. Select a model and click Add Folder.

2. Enter a folder name and click OK.

How to Delete a Folder

1. Select the folder.

2. Make sure there are no calculations in the folder as you cannot delete a folder that contains calculations.

3. Right click and select Delete.

How to Rename a Folder

1. Select the folder.

2. Right-click and select Rename.

3. Enter the new name of the folder then click OK.

Run

Run the Calculation. Saving the Calculation will not execute the script.

Variables Manager

Select values for variables defined in Calculations.

See:

Change Variable Values for Types that Share the Same Variables and Values

Selecting a Value for a Variable

Scheduler

Manager Select the Schedule Pattern value field and click Scheduler Manager to schedule a Calculation to run at specific time intervals. Once the Calculation is complete, notifications are sent to selected users.

You can select Weekly, Monthly, or Daily frequencies.

See: How to Schedule a Calculation

Refresh

After a selection is made, click Refresh to view updated data.

Description of Spreadsheet Fields

A description of spreadsheet fields and actions is provided below.

Model

Select the model for which you want to define the calculation.

Name

Select an already defined calculation or select New Calculation and enter a name for the calculation.

Run in Background

Yes - Run processes in the background (for example, an aggregation or calculation that will take a long time to execute). See the Request Status page, accessed by selected the Manage task and the Request Status subtask, for process details.

No - Do not run processes in the background.

Current Status

This field is updated when you select No for Run in background and provides status information on the results of the run (for example, successful).

Last Run

Time This field is updated when you select No for Run in background and provides run time and date information.

Last Run Status

This field is updated when you select No for Run in background and provides information on the time to complete the most recent run.

Schedule Pattern

Click the Schedule Manger action to select a schedule pattern. The Schedule Pattern field is populated based on the selections you make in Scheduler Manager. It reflects the frequency, time, and day pattern for which you want to run the Calculation.

Schedule pattern is displayed in following format:

Daily : "Calculation scheduled to run Daily on every 1 day(s) at <Time> <AM/PM>"

Weekly : "Calculation scheduled to run Weekly on following day(s): <name of day(s)>, at <Time> <AM/PM>. Beginning on <Begin time> and ending on <End time>"

Monthly :

"Calculation scheduled to run Monthly on following day(s): '< n> ' at <Time><AM/PM>."

where:

<Time> denotes the time at the process is scheduled to run

<name of days(s)> denotes the names of the days in a week when the process is scheduled to run.

<Begin time and End time> denotes the start and end times for a process to run.

<n> denotes day number in a month when a process is scheduled to run.

Success / Failure Notification

The Success Notification and Failure Notification fields are also populated based on the users you select in Scheduler Manager.

Description

Provide detailed information about the Calculation.

Type

The type you select depends upon the type of task you want to execute. Options are described under Calculation Types.

Name

The name that identifies the type.

Model

Select the model you want to modify.

Variables

Add substitution variables created on the Model task Substitution Variable subtask.

Description

Add detailed information for each step within the Calculation.

Calculation 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 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 on a monthly basis. 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. Access 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 are shown below. Notice the full refresh clears the model and generates it again while the partial refresh appends the metadata.

An example of typical partial refresh Calculation steps are shown below.

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 prior to 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 formula 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.

Creating a Calculation

1. In SpotlightXL, select the Model task and the Calculation subtask.

2. Select a model from the Model list box.

3. Enter a name for the calculation or select an existing calculation from the Name list box.

4. Select a type.

5. Enter a descriptive name in the Name list box to identify the type.

   In the image below, 3 formulas are populated from the Planful Structured Planning, Consolidation, or Reporting application model. The formulas are executed in sequential order starting with Aggregation.

   

   The page below shows how a formula translates to the Calculation. The FinanceMaster Account Formula is created on the Formula page. It is then added to the Calculation page as shown below. In this example, the Calculation will process the account and company formulas and then calculate year to date data. The FinanceMaster Account Formula calculates gross sales using the Royalty Calculation, which multiplies gross sales times .03. It adds asset acquisitions, asset disposals, and asset transfers to obtain a total for Prop Plant & Equip Change in Bal.

   

6. Select the model you want to modify.

7. Click Variables Manager. The Variables Manager screenappears. Select a new variable value. See the "Selecting a Value for a Variable " topic below.

8. Click Save.

9. Click Run to execute the Calculation.

Sample Calculations

1. Year to Date (YTD) – perform the following steps:

   1. Aggregate the model

   2. Execute the YTD formula

2. Refresh data from Planful Structured Planning, Consolidation, or Reporting applications – to refresh Dynamic Planning models with the most up-to-date data and metadata from the Planful Structured Planning, Consolidation, or Reporting applications, consider the following example:

   1. DownloadMetadata

   2. Download Data

   3. ClearModel

   4. GenerateModel

   5. Aggregate

   6. Formula – YTD

3. Perform a multi-step allocation – perform a calculation which might be a series of steps that need to occur.

   1. Map – move data to calculate headcount as a percentage of total

   2. Formula – calculate allocation percentage

   3. Map – move shared services data to the same level as allocation percentage

   4. Formula - calculate allocated expense (Allocation % * Shared Services expense)

   5. Formula – calculate contra allocation expense (Shared Services expense * -1)

Clear Data Performance Optimization

The ClearAllData Calculation step has been enhanced to process the data quickly, saving a lot of time working with extensive data calculations. Previously, the calculation step used to take a considerable amount of time to clear models with a large amount of data.

Running Calculations in the Background for Views and Reports

Views and Reports can run a calculation when the user clicks Save. By default, when a calculation is run from a view or report, it runs in the foreground, which prevents users from continuing to work. However, Power users can set an application-wide setting to run calculations in the background for all views and reports that trigger calculations. Those calculations must have been created with the Run in Background field set to Yes. After the calculation has completed its processing, an email is automatically sent to the user that triggered it.

Our recommendation is to set this option to Yes.

1. Select Manage, Application Administration, Application Settings. Scroll down to the Calculation Property section. After making changes, click Save. Then logoff and login again to see the effect of the changes.

   

2. Select Model, Calculation to ensure that the calculation was set up with Run in Background set to Yes.

   

3. You can see a list of all calculations and their current background setting by choosing Model, Setup. Select the model, then scroll down to the Calculations section.

   

4. Verify that the calculation is set up to run when the user clicks Save from a view or report by reviewing the view or report properties.

   

   

5. When the user clicks Save in the view or report, they see the following message:

   

   Note:

   The view or report no longer refreshes the data automatically after the user clicks Save.

Using Scope with Aggregation in Calculations

With scope, you can aggregate only what you require. Process and refresh time is reduced.

The following image shows that a scope named Download 2015 is created on the November model. The Actual dimension is filtered for the Scenario dimension. For time, all members except for 2015 are filtered. This scope will be added to the DownloadData Type on the Calculation so that only Actual scenario data for 2015 is downloaded from Planful Structured Planning, Consolidation, or Reporting applications to SpotlightXL.

The following image displays the set up to download only 2015 Actual data from the Structured Planning, Consolidation, or Reporting application to Dynamic Planning.

The following image displays the aggregation for the 2015 Budget Scenario.

Using the Variables Manager

The Variables Manager allows Contributors to make choices of what areas of the model to calculate. For example, a variable called @CurQtr@ could be used to calculate a formula for only the current quarter rather than every quarter in the Time dimension.

Selecting a Value for a Variable

To select a value for a variable in a Calculation:

1. Select Model, Calculation.

2. Select the Model you wish to modify (e.g., 2013 All Products).

3. Select the cell for the variable you wish to add a value to, (i.e., a variable for an existing Map). In the example below, cell D10 is selected and has two variables: @CurScenario@ and @FYYear@.

4. Click Variables Manager.

5. Select a variable from the list (e.g., @CurScenario@ ) then double-click the New Value row for the variable. A list of substitution variables and scenarios appears.

6. Highlight the new value you wish to assign to the variable then click Select . In the example shown, we are replacing the value Actual for variable @CurScenario@ with 2012 Budget.

7. Click Update. The new value is added to the variable, replacing the previous one.

   

   
   

Note: The Variable names must be different for both the source and target model of a Map.

Change Variable Values for Types that Share the Same Variables and Values

1. Select the cells for the variable you wish to add values. In the example below, cells D10 and D11 are selected and share the same value 2013 for variable @FYYear@.

2. Click Variables Manager. The Variables Manager screen appears.

3. Select a variable from the list (e.g., @FYYear@ ) then double-click the New Value row for the variable. A list of substitution variables and scenarios appears.

4. Highlight the new value you wish to assign to the variable then click Select. In the example shown, we are replacing the value 2013 with 2014.

5. Click Update. The new value is added to the variables in both lines, replacing the previous one.

   

   Note:

   Skipping of lines, as defined in the Map (#skip# ), is supported.

Using the Calculation Variables Manager #SKIP# Option

Power and Contributor users can use the SKIP option for variables in a calculation. This functionality is available from both Model Manager and SpotlightXL.

Use the SKIP option when you have a Map or Formula that uses variables (usually multiple variables). You can build one master map or formula to accommodate data loading or formulas for a whole series of data intersections. Then when the calculation is run that executes the formula or map, you can skip those data intersections that do not need to be loaded or calculated.

Consider the following formula with two variables and the calculation that runs it. We will set the calculation to skip the @NextQtr@ variable.

1. Login to SpotlightXL.

2. Select the model and calculation that uses the formula or map variable(s).

3. Click the Variables Manager button on the menu ribbon.

   

4. Select the variable that you want to skip, click the member selection icon, then select Skip.

   

   After you select Skip, you see #SKIP# as the New Value of the variable. Select a value for other variables at this time also.

   

5. Click Update when you are done with your changes.

   This calculation will now calculate Sales as ASP * Units for the member intersections defined in the formula. In the Time dimension, only Q3 2018 will be calculated.

Providing Calculation Access to Contributors

To provide Contributors with the ability to create and modify calculations:

1. Navigate to Manage, Application Administration, Application Settings.

2. Scroll down to the Calculations heading as shown below.

   

3. Set the Enable User Group Permissions property to Yes.

   Power users can assign Groups to the Calculation. Only Contributors belonging to the assigned Groups will be able to access, modify, and run the Calculation. For example, a Power user assigned the Test1 Calculation to Contributors of Group A as shown below.

   

   Now, notice that Group A is not assigned to the Test2 Calculation.

   

   When a Contributor logs into the application and accesses the Calculation page, only Test1 is available as shown below.

   

Scheduling a Calculation

The Schedule Pattern field is populated based on the selections you make in Scheduler Manager. It reflects the frequency, time, and day pattern for which you want to run the Calculation.

The Success Notification and Failure Notification fields are also populated based on the users you select in Scheduler Manager.

To schedule a Calculation:

1. Access the Model task and the Calculation subtask.

2. Select the Model and Calculation you want to run.

3. Click the Scheduler Manager action.

4. Select the Frequency, time (At), and Begin and End options.

5. Select the month/day/week interval.

6. All Dynamic Planning users are listed. Select the users you want successful (Succeeds) and unsuccessful (Fails) process information sent to.

7. Click Save.

Data Downloading with a Scope

Use Scope to trim your data download based on your particular requirements (for example, download 2019 but do not include 2020); subsequently, your data refresh times are reduced.

See: Scope

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.