Running and Filtering Calculations

This option is available for Power and Contributor users. Reviewer users cannot view calculations.

How to Run a Calculation from the Model Manager?

1. In Spotlight Web, navigate to Dynamic Planning > Model.
2. From a Model Box, click the Calculations artifact.
3. Select the calculation you want to open.

How to Run a Calculation from the Model Artifacts Browser?

1. In Spotlight Web, navigate to Dynamic Planning > Model.
2. From a Model Box or Model List layout, select the More menu, then select Model Artifacts.
3. Click Calculations.
4. Select the calculation you want to open, then click the Run icon.

How to Filter or Search for a Calculation?

1. In Spotlight Web, navigate to Dynamic Planning > Model.
2. From a Model Box or Model List layout, select the More menu, then select Model Artifacts.
3. Click Calculations.
4. Click the Filter icon.
5. Type a few characters in the calculation name. The Model Artifacts Browser dynamically refreshes to show you which calculations have those characters in their name.

Viewing or Changing the Value of a Variable in a Calculation

The Model Manager provides the ability to view and set variables in calculations using the Variable Manager. This functionality is also available in SpotlightXL.

You can view the current value for the variable and optionally specify a new value. This is useful for organizations that want to set up a calculation and run it regularly, using a variable to specify what is different each time you run it. For example, the calculation may run an aggregation for a department, or for just a particular year, where the department or year are variables.

Consider the following example:

• Everest Cycles is a model that contains financial data for an international company that sells bicycles.
• The model needs to be aggregated quarterly.
• Using SpotlightXL, you create a Scope for the model that defines a variable called @LatestQuarter@, based on the Time dimension.

• You create a Calculation that runs an aggregation for the scope of time specified in the variable @LatestQuarter@. 

In Model Manager, the variable used in the calculation can be modified at runtime so that the calculation aggregates whichever quarter the user choose.

How to Access and Manage Calculations in the Model Artifacts Browser in Spotlight Web?

1. In Spotlight Web, navigate to Dynamic Planning > Model.

2. Select Model from the task menu.

3. Select the Calculations artifact from the Model box and pick one from the list to open the Model Artifacts Browser.

   

4. The Model Artifacts Browser lists the calculations. Included is an icon, which opens the Variable Manager.

5. Click the Variable Manager icon to open the Variable Manager screen.

   The icons along the top are:

   1. Return to Model Artifacts.

   2. Refresh to the last saved version.

   3. Save changes to the calculation.

   4. Save changes to the calculation and Run it.

   5. Run the calculation.

   6. The Calculation Name is provided along with a drop-down if you would like to switch to viewing a different calculation. For information on how calculation steps correspond to the rows in a calculation, see Calculation Steps in Model Manager.

6. Click the open box icon to select a new value for the variable.

7. In the dimension browser pane on the right, search or click through to find the quarter that you want to calculate.

8. Click Select Member. The selected member then appears under the Value column.

9. Click Save or Save and Run. A message appears to confirm your selection.

10. Click to Return to Model Artifacts when done.

Calculation Steps in Model Manager

In Model Manager, you can set the value of variables used in calculations.

Select the calculation and click the Variables Manager icon to open the Variable Manager screen.

Under Calculation Step, you see the list of All variables used in the calculation. These steps correspond to rows in the calculation in SpotlightXL that include variables. By default, you see All variables that are used in all steps of the calculation. You can set them all at once, or set them step by step.

The Variable Manager screen does not show you all the rows in the calculation, only those that include variables. For example, the full calculation for the Detailed Revenue calculation above includes several rows that do not use variables. Only the 6 rows that include variables are included in the Variable Manager.

You can use the drop-down to select different steps in the calculation and set variable values for each. Or you can select All and set all variable values at once.

How to Make Changes to Variables in Spotlight Web?

1. In Spotlight Web, navigate to Dynamic Planning > Model.

2. On the required model box, select the required calculation.

3. On the calculation window, select a variable.

4. Select the dimension member you want to use in place of the variable.

5. Click Select Member.

   

6. Continue by changing variables in as many steps as you want.

7. Click Save or Save and Run when done.

   To assign a substitution variable to a variable :

   You can also use a substitution variable to change the value for a variable instead of using Variable Manager. You can assign the substitution variable to the variable once in the calculation, and then you only need to change the value of the substitution variable going forward.

How to Assign Substitution Variable to a Variable?

1. In Spotlight Web, navigate to Dynamic Planning > Model.
2. On the required model box, select the required calculation.
3. On the calculation window, select a variable.
4. Select a variable.
5. Select the substitution variable you want to use in place of the variable. (The substitution variable must be predefined.)
6. Click Select Member.
   
7. Click Save or Save and Run when done.

Editing a Breakback in a Calculation

You can review and update Breakback steps in a calculation using Model Manager in Spotlight. We recommend that you use SpotlightXL to create the Breakback step in the calculation, but after that, you can edit the Breakback on the web.

How to See the Breakback Steps in a Calculation on Spotlight Web?

1. Login to Spotlight and select Model from the task menu.

2. Select the model that has the Breakback operation in a calculation, then click on that calculation. If you are not sure which calculations include Breakback, then open the Artifacts Browser.

3. In the Model Artifacts Browser, as you select different calculations in the list, those that include a Breakback operation will show a Breakback icon on the icon bar.

   

4. Click the Breakback icon. The Model Artifacts Browser expands to display the Breakback steps under "Edit Breakback for Calculation Step."

   

   If the calculation includes more than one Breakback operation, you can toggle between them by selecting the step using the drop-down.

   

   As a reminder, Breakback operations include several pieces of information, all of which you can view or edit in the Breakback pane. The list below shows the different elements of Breakback.

• Model name

• Parent-level data intersection from which to allocate data to children

• Spread method

• One or more parent-level dimension members, if using the Reference Spread method

• Type of change to make: amount or percentage increase/decrease

• Value of the change to make: an inputted value or by looking up a reference to a number in a member intersection

• Hold members (child members that will be skipped in the Breakback)

After you make changes to the Breakback operation:

• Click Save

• Or click Save and Run

Using Substitution Variables in Breakback Operations from a Calculation

You can use substitution variables in place of member names in Breakback operations within a calculation.

This feature is supported both in SpotlightXL and Spotlight.

When defining Breakback operations in a calculation, you can use the member select icon to select a Substitution Variable instead of a fixed member name in the Breakback Cell:

You can use Substitution Variables in the Reference Spread also:

You can use Substitution Variables in the Hold section also:

Using the Calculation Variables Manager #SKIP# Option

Power and Contributor 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.

In Practice

1. In Spotlight Web, navigate to Dynamic Planning > Model.

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

3. Click the pencil icon to open the Variables Manager.

4. Select the variable that you want to skip, and click the Skip option under the member selection box.

   

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

   

5. Click Save 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.