Dynamic Planning Model Formulas

Overview

With this subtask, you can define formulas on any dimension member within a single dimension. No cross-dimensional formulas are supported at this time. You can have multiple formulas per model. Formulas are the building blocks of calculations. The calculation defines the logical order in which formulas are executed.

Actions Available

The table below provides a description of the actions available for the Formula subtask.

The table below describes the spreadsheet fields on the Formula page.

Define a Formula

In Practice

1. Select the Model task and the Formula subtask.
2. Select the model for which you want to define the formula from the Model list box.
3. Select the dimension, which contains the data you want to perform the formula and calculation on.
4. Enter a name to identify the formula when adding a new formula.
5. Identify the dimensions for which the formula will be applied.
6. Select filter properties to apply to the formula. The following options are available:
   1. AllMembers – The formula will apply to all members that are part of the dimension.
   2. LevelandAbove – The formula will apply to the level and above the filtered value.
   3. MemberandBelow – The formula will apply to all data for the selected value and below (such as leaf level).
   4. FixedMember – The formula will apply to a specific member of the dimension.
7. Based on Filter selected, enter a value in the Filter Value field. For example, if FixedMember is selected as the Filter for Scenario and you want the formula applied to the Actual scenario, you would enter Actual in the Filter Value field.
8. In the Formula Design cells, enter formulas for calculated dimensions.
9. Run the calculation on the Calculation page when new formulas are included. First, add the formula to the calculation and then run the calculation.

Create formulas using Microsoft Excel syntax. The following functions are supported in SpotlightXL formulas:

• Arithmetic Operators: + - / *
• Logical Operators used with IF function:= <> < <= > <=
• Functions: IF/THEN, AND, OR, ROUND, FLOOR, CEILING, LN, ISBLANK, ABS, SQRT, POWER, MOD

Using Variables in Views and Reports

Variables can be assigned to Views and Reports, which will be used during the execution by the Calculation and will make them more dynamic. For example, assume you create a map, formula, and scope that use a variable called @CrBudget@. Then, you create a Calculation that uses these artifacts (the map, formula, scope) and the variable called @CrBudget@. When you save data from a View or Report where the @CrBudget@ variable is used, the application passes the selected scenario for execution to the Calculation.
When Variables are used in Views and Reports, for example @CurScenario@, the current scenario is passed to the Calculation. For example, assume you want to save data for several scenarios. Use a variable and pass the variable automatically from a View or Report without accessing the Calculation and manually changing it.

See Also: Defining Substitution Variables

Example

In this example, the @CurScenario@ variable is defined. When data input is performed in the view on the Analyze Data page, the Calculation runs automatically no matter what scenario is selected (Actual or Budget).

The model and view shown below, called Variables Example - Exg Input View, is used to input exchange rate information for local currency for the Actual and Budget scenario. The system calculates the common currency data based on exchange rate and local currency. So, the input is exchange rate and local currency and the output is common currency. Notice that the exchange rate information for the Actual scenario is shown below.

Select the Budget scenario and exchange rate information for that scenario is displayed.

Now, the Data Input View is displayed to perform data input for the Actual scenario as shown below.

As you input data in this View, the Calculation is running in the background to automatically calculate the common currency.

When the validation view, called CC Date, is accessed, the common currency data is available for the Actual scenario as shown below.

Now, you can select the Budget scenario and input data. As you input data, the Calculation will automatically calculate the Common Currency for the Budget scenario as well. This is because the @CurScenario@ variable is used so the Calculation will execute automatically for whichever scenario is selected as the current scenario.

To Assign Variables to Views

In Practice

1. Access the Design View page.
2. Select the Properties action. The Variable Name and Dimension fields are displayed.
3. Enter variable name and select the associated dimension that is defined in the map, formula, and scope. In the example below, the @Scenario@ variable is entered and the Scenario dimension.

Prerequisite

You must have the variable defined in your formula (accessed by navigating to the Model Formula page). You must use the same variable in your Calculation.

In the example below, the Filter Value on the Model Formula page contains variables for Region and Scenario dimensions.

On the Model Calculation page, there are variables defined for the Formula and Aggregation steps. Budget is specified for the @CurScenario@ variable and North Region is specified for the @CurRegion@ scenario.

When the Budget Model and View is accessed via the Analyze Data page, you can enter the @CurRegion@ and @CurScenario@ variables in the Properties page for the View. In the image below, these variables have been entered in the Properties page and the dimension members selected. Notice that the North Region and the Budget variables are displayed on the page axis.

How to Use Variables and Variable Expressions in a Formula

You can use both variables and variable expressions in Maps, Formulas, and Scopes.

To see details about how expressions work, their syntax, and how to chain them together, see Example Substitution Variable Expressions.

Variables and Expressions are supported in both SpotlightXL and Spotlight.
Note: The Variable names must be different for both the source and target model.

Variables and Expressions in Formulas

The following examples show how to use variables and expressions in formulas. The variables must be associated with the dimension selected in the Dimension field.

You must specify the value of the variable in the calculation that runs the formula.

This example subtracts the Scenario pointed to by the @Budget@ variable from the Actual and places the resulting value in Budget Variance.

The following example shows a variable expression used in a Formula. It is based on a @Year@ variable defined on the Time dimension. This example copies the ASP Account for the Plan Scenario from the first month of the year specified by the variable @Year@ to the next 5 months of the same year. The formula for each of the month rows is the same; it simply points to the value for the first month.

• @Year@.FirstChild.FirstChild equates to the first month of the first quarter in the year.
• @Year@.FirstChild.FirstChild.Lead(1) equates to the second month of the first quarter of the year, and so on.
• Note that even though Lead 3, 4 and 5 are not months in the first quarter, they operate as if they were.

The calculation that runs this formula is as follows:

Using Substitution Variables in Variable Expressions in Formulas with a Formula Reference

Contributors can use substitution variables in variable expressions in the Formula Reference section of formulas. Variable expressions make it possible to refer to members in a dimension that are relative to another member.

In the following example, @PreferredProduct@ is a substitution variable that defaults to the Default product. This substitution variable value will be changed in the Calculation prior to executing the formula.

The formula calculates Sales as ASP * Units.

The formula will use the ASP for the product referred to by "@PreferredProduct@.FirstChild.Lead(1).FirstChild.FirstChild" in all the calculations.

The Product dimension is as follows. @PreferredProduct@ will be defined as Product in the calculation. The expression evaluates to:

Product (@PreferredProduct@)

• Mountain Bikes (FirstChild)
  • Men's Mountain Bikes
  • Women's Mountain Bikes
• Road Bikes (Lead(1))
  • Men's Road Bikes (FirstChild)
    • Classic RB - Men (FirstChild) - this is the product whose ASP will be used for all iterations of the formula.
    • Triathlon RB - Men
  • Women's Road Bikes
    • Classic RB - Women
    • Triathlon RB - Women

Note that you can use the Reference Manager to see if you have made an error in your expression.

Here is a snapshot of data before the formula is run. The Sales values are not filled in.

Here is the resulting data after the formula is run. The green boxes show the ASP values for different intersections of Classic RB-Men. These values are used to calculate Sales for all other products as well, such as the Triathlon RB-Men. For example, Sales for Triathlon RB-Men in the East region in Jan-17 would normally be calculated as 6 * 108 = 648. But since the Formula Reference section specified that the Classic RB-Men ASP should be used in all cases, the Sales in this case is calculated as 1 * 108 for Jan-17, 2 * 108 for Feb-17, 3 * 108 for Mar-17, as shown in the red boxes.

To see details about how expressions work, their syntax, and how to chain them together, see Example Substitution Variable Expressions.

Using Attributes in Formulas

For information on using Attributes in Formulas, see Using Attributes in Formulas to Identify Cells to Calculate.

View Formula Used for Calculation in Views and Reports

You can view how a value at a particular cell intersection is being calculated by viewing the formula in the formula bar. For example, for all cell values that are derived using a calculation, you can view the formula associated with the calculation directly in the formula bar, while all cell values that do not have any calculation associated with them, you will see the respective cell values in the formula bar.

The following image displays the formula in the formula bar of SpotlightXL.

How does the functionality work

While you are working on any Report or View, the application constantly validates each cell intersection and verifies if any formula is defined within the calculation that is mapped to the artifact. For example, if your calculation contains formula, aggregation, map, or some other step, the application validates all these steps, finds the exact dimension intersection based on the scope, and then retrieves the formula and displays it in the formula bar.

Let us take the following example to understand the details.

If you have a View or Report containing different details such as Topwear, Bottomwear, Footwear, and so on for different seasons, and you are using these details to calculate the total for the “Men” section, then you can view the formula associated with the calculation in the formula bar as shown in the image below.

In the example above, for displaying the formula associated with the calculation for the “Men” section, the application first validates the properties to check if the Enable Save property is set to Yes and whether a Calculation is mapped to this particular View or Report as shown in the image below.

Once the application identifies a Calculation, the application next verifies all steps available in the calculation and checks if a formula is available in it. In the example above, SubVarFormula is the formula associated with the calculation for the “Men” section as shown in the image below.

Next, the application validates the formula details available in the Formula property and displays the formula in the formula bar. In the example above, the application validates details such as Formula Design, Formula Members, Dimensions, and so on for SubVarFormula, and displays the formula in the formula bar.

This functionality is applicable for a View or Report where save is enabled and the artifact has a calculation associated to it. However, this functionality is not applicable for any View or Report that is for analysis or reporting purposes.

In Practice

1. In Spotlight Web or SpotlightXL, select a View and navigate to any cell intersection that has a calculation associated with it.
2. View the formula in the formula bar.
   
   

Formula Performance Optimization

We have improved the performance of formula steps calculations for all formula members. If the "Enable Formula Performance" flag is set to true, a new formula code will be applied, allowing you to calculate the formula reference for members based on specified keys. Formula creation with formula reference took longer for high-value dimension combinations. In addition, we improved the formula run-time by skipping a few overheads, significantly reducing the time and effort involved in the execution of formulas for separate formula members.

Limitations

1. If the Enable Save property is set to Yes and the Calculation on Save property is configured with a calculation, then the formula configured in the calculation will be displayed in the formula bar. For any other calculation where the Enable Save property is not set to Yes, then the formula will not be displayed.
2. If the Enable Save property is set to Yes and the Calculation on Save property is configured with a calculation. Still, if the application runs a different calculation, then the formula displayed in the formula bar and the formula configured in the calculation will not match.
3. If multiple formulas are configured for any intersection, then the application will display only one formula in the formula bar.
4. The way in which the formula is displayed in the SpotlightXL varies slightly with the way in which it is displayed in the Spotlight Web. The formula in the SpotlightXL starts with an additional =@SpotlightFormula in SpotlightXL.