Using 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.

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 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, 2 * 108 for Feb, 3 * 108 for Mar, 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.