Creating and Running Calculations

How to Create 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.
6. Select the model you want to modify.
7. Click Variables Manager. The Variables Manager screen appears. 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 the total
   2. Formula – calculate allocation percentage
   3. Map – move shared services data to the same level as the 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. 
2. Scroll down to the Calculation Property section. After making changes, click Save. Then logoff and login again to see the effect of the changes.
   
   
   
3. Select Model > Calculation to ensure that the calculation was set up with Run in Background set to Yes.
   
   
   
4. 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.
   
   
   
5. 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.
   
   
   
   
   
6. When the user clicks Save in the view or report, they see the following message:
   
   
   

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 My Scope was created on the Allocation model. 

The following image displays the aggregation.

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.

How to Select 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.
3. Select the cell for the variable you wish to add a value to.
4. Click Variables Manager.
5. Select a variable from the list then double-click the New Value row for the variable. A list of substitution variables appears.
6. Highlight the new value you wish to assign to the variable then click Select. 
7. Click Update. The new value is added to the variable, replacing the previous one.
   

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 the member intersections defined in the formula. In the Time dimension, only selected period will be calculated.

How to Provide a Calculation Access to Contributors?

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

1. In SpotlightXL, 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. 

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.

How to Schedule 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 when 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. In SpotlightXL, navigate to the Model > Calculation.
2. Select the Model and Calculation you want to run.
   
3. Click Scheduler Manager.
   
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 to whom you want successful (Succeeds) and unsuccessful (Fails) process information sent. 
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.