Using SpotlightXL-Model Calculation Subtask

Use Reference Manager to reference a dimensional intersection from any dimension within the Model.

Use Case: You want to calculate the amount for Quantity and Price for all default products, but Quantity and Price dimensions are located at two different dimension intersections (Budget Model; Qty-Budget Input view and Price-Budget Input).

The Qty-Budget Input view is displayed below and has the Time, Product and Region dimensions and Quantity is captured based on these dimensions.

The Price-Budget Input view is displayed below with Region and Price dimensions.

The formula is quantity times prices as shown below.

However, you do not want to use the price for each individual product but rather the price of the Default product.

Copy Price to the Formula Member line. Select the Formula Reference cell, and click the Reference Manager button. Select the Default member for Product as shown below.

When the formula is executed, it takes the Price available at the ‘Default’ product and multiplies it by the Units for each specific product, region, and month.

Amount after executing the formula (Qty * Price) shown below.

Qty measure – Data available by Region and by Product.

Price measure – Data available by Region for ‘Default’ Product only.

You can also use variables, variable expressions, and substitution variables in the Formula Reference section. See Using Substitution Variables in Variable Expressions in Formulas with a Formula Reference.

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

Save defined formula data.

Delete a selected formula.

Model

The name of the model that contains the dimension for which the formula will be applied.

Dimension

The name of the dimension for which the formula will be applied.

Name

The name of the formula.

Description

An optional description of the formula.

Created On and By

Automatically populated fields to show who created the formula and when.

Modified On and By

Automatically populated fields to show who last modified the formula and when.

Dimension

The dimension members the formula will be applied to. You can have the same dimension listed multiple times with different filters applied. This section essentially defines the scope of the formula.

In the example below, the Time dimension is listed twice. The filter is FixedMember for each and is set to Q1 and Q2. This means that this formula will be calculated with Time dimension data for only these two quarters.

Another example is provided below where, in addition to Time, the Department dimension is filtered so that data from the Product Departments and Design Depts and all members below will be included in the formula calculation.

See: Using Variables in Views and Reports

Filter

Select how you want the members of the dimension selected. For example, do you want to select all dimension members or a specified (fixed) dimension member.

• AllMembers – The formula will apply to all members that are part of the dimension.
• LevelandAbove – The formula will apply to the level and above the filtered value.
• MemberandBelow – The formula will apply to all data for the selected dimension member its descendants (such as children and leaf level members).
• FixedMember – The formula will apply to a specific member of the dimension.
• LeafMembers - The formula will apply to leaf-level members only. This is useful in advanced Dynamic Planning use cases, for example, when performing aggregation as part of the calculation, so that the impact of the formula is applicable for all roll-up members in the dimension.

Filter Value

Based on the 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 or select it from the drop-down.

If Filter is AllMembers, Filter Value must be None.

If Filter is FixedMember, LevelAndAbove, MemberAndBelow, or LeafMembers, Filter Value must be a dimension member name or an expression that renders a dimension member name.

Formula Design

In this section, you enter the formula associated with the dimension selected in the Dimension field. That dimension is automatically indicated in column A and the word Formula is indicated in column B.

Used the drop-down to select the name of the dimension member that will hold the result of the formula, which we will call the calculated member. Then add rows as needed above the calculated member to specify any and all dimension members that are needed in the formula.

In the example below, the calculated member is Forecast.

A row is added above Forecast, and Actual is selected. This indicates that the formula used to calculate Forecast is dependent on the data in Actual.

In column B next to the calculated member (the member that will contain the result of the formula), enter the formula using Excel-style syntax. Assume that the data is in column B of the other dimension members listed.

In the example below, a formula (on Forecast) is defined on a Scenario dimension. The formula is Actual multiplied by 1.5. The formula will affect all members but only for the selected period.

In this section, you can also use Variables and Expressions, so long as they are variables on the dimension selected in the Dimension field.

In the following example, @Year@ is a variable defined on the Time dimension and it is copied to five other months.

• @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.

After running the formula via a calculation, you can see the changes. For more information, see How to Use Variables and Variable Expressions in a Formula.

Formula Member

Specifies a dimension member that is used in the formula. This must be one of the members listed in column A under Formula Design.

Formula Reference

Defines the member of the dimension specified to be used by default in the formula. After selecting the Formula Member in column A of this section, select the cell under Formula Reference. Then click the Reference Manager button. Use the Reference Manager to select a particular dimension member that will be used as the reference anytime the Formula Member is used in the formula calculation.

For example, instead of using the Price associated with each member intersection of Region, Product, Scenario, and Time, you can specify to use only the Price associated with the Default Product.

See Reference Manager for details.