Calculation Editor

The SAP Analytics Cloud Calculation Editor allows you to create calculations for use in a chart or table.

In a table, you apply calculations either to the Account or to the Cross Calculations dimension. For each type of calculation, a new calculated or restricted member is created for the dimension that you used to create it. You can also use dimension attributes as part of a calculation. For more information about dimension attributes, see Import and Prepare Fact Data for a Classic Account Model, and Import and Prepare Fact Data for a Model with Measures.

Note
The number format chosen in Start of the navigation pathProfile Settings Next navigation step User PreferencesEnd of the navigation path influences the expected input format for Story Calculated Measures and Calculated Dimensions:

  • Choosing a number format that uses periods (.) as decimal separators means that commas (,) must be used to separate function parameters (for example, IF(Condition, ValueIfConditionIsTrue, ValueIfConditionIsFalse)).

  • Choosing a number format that uses commas (,) as decimal separators means that semi-colons (;) must be used to separate function parameters (for example, IF(Condition; ValueIfConditionIsTrue; ValueIfConditionIsFalse)).

If the formula is typed in from scratch, the correct function auto-completion happens based on the user preferences. However, if you copy and paste a full formula string, auto-complete won't be able to adapt if there is a mismatch between separators used and the user preferences.

You can display the calculation in a table by adding the Account or Cross Calculations dimension to the table, or by selecting it in the filter applied to the dimension.

In a chart, calculations based on the Account dimension can be added as new measures.

The following calculation types are supported:

  • Create Calculated Measures: Perform a calculation on one or more members of either the Account dimension or the Cross Calculations dimension. A new calculated member of the selected dimension is created as a result.

    Restriction

    If the calculation is based on the Cross Calculations dimension then dimension members or attributes from the Account dimension cannot be used.

    If the calculation is based on the Account dimension then dimension members from the Cross Calculations dimension cannot be used.

  • Create Restricted Measures: Restrict the data from a member of either the Account dimension or the Cross Calculations dimension so that it excludes certain members of one or more dimensions. For the Date dimension, you can pick dynamic values such as year-to-date or previous quarter. A new restricted member of the selected dimension is created as a result.

    Note

    When using data from SAP BW, creating a restricted measure on another restricted measure (referred to as a key figure in SAP BW) that uses the same dimension results in incorrect data in SAP Analytics Cloud. A further restriction is not possible because SAP BW treats restrictions as OR operations. In most cases restricted measures are intended to get the result as an AND operation.

    For example, COUNTRY is restricted to Great Britain and Germany. In a sales scenario, the intended result would be that you see products that are sold in both countries (Great Britain OR Germany) and not a combination of either country and both of them (Great Britain OR Germany OR (Great Britain AND Germany)).

  • Calculating the Difference: Find the difference in an account's value between two dates. A new calculated account member is created as a result.

  • Create an Aggregation: Create calculations from aggregations such as sum, count, average, and so on. Choose what conditions are required for the aggregation to be applied, and when the conditions are required.

  • Date Difference: In a chart, calculate the time interval between two dates.

  • Perform string and number conversions: convert strings to numbers and numbers to strings, and use the resulting values in calculated measures or dimensions.

  • Create calculated dimensions: The string and number conversion functions can be combined with measure-based dimensions to create measure to dimension conversions.

  • Currency conversions: For planning models with currency conversion enabled, add a new currency conversion to the Cross Calculations dimension. For more information, see Displaying Currencies in Tables.

Planning models also support the following forecast calculations:

  • Forecast

  • Rolling Forecast

For more information, see Creating a Forecast or Rolling Forecast Layout

Calculations based on input controls

Calculations can use input controls. Input controls provide variable input for a calculation, allowing viewers to influence the result of a calculation without modifying the underlying data or formula. For example, viewers can choose to see the impact of a 1%, 2%, or 3% tax-rate increase. You choose the list of values for an input control, and specify how the user can select values. Input controls can be formatted after they are added to the canvas.

Tip
The Calculation Editor dialog will change size to fit the calculation type information fields and re-center itself on the page.
Note

Duplicate and Edit Calculations

There are times that you may want multiple calculations that are almost the same. For example, you may want restricted measures with different time filters. It takes time to create a calculation and you have to create each calculation separately from start to finish, even if you only need to change one small parameter.

A faster way to create multiple calculations would be to duplicate an existing calculation and then edit it.

Note
Only the following calculations can be duplicated:
  • calculated measures
  • restricted measures
  • calculated dimensions
  • cross calculations
  1. Set up the calculation for editing or copying.
    Item Duplicate Edit
    Select a chart
    1. In the Builder, select a calculation.
    2. Select Start of the navigation path Next navigation step DuplicateEnd of the navigation path.
    1. In the Builder, select a calculation.
    2. Select Start of the navigation path Next navigation step  Edit CalculationEnd of the navigation path.
    Select a table

    1. In the Builder, expand the Story Calculations list. (Story Calculations are listed with measures or with cross calculations.)

    2. Select the calculation and then select (Duplicate calculation).

    1. In the Builder, expand the Story Calculations list. (Story Calculations are listed with measures or with cross calculations.)

    2. Select the calculation and then select (Edit calculation).

  2. Make changes to the calculation. (You can't change the calculation type.)

  3. Select OK.

Format a Calculation

You can format the results of a calculation in a chart or table.

  1. Set up the calculation for formatting.
    Item Select Format
    Select a chart
    1. In the Builder, select a calculation.
    2. Select Start of the navigation path Next navigation step  Format...End of the navigation path.
    Select a table
    1. In the Builder, select Story Calculations and expand the calculation list.
    2. Select the calculation and then select (Edit formatting options).

  2. Modify the formatting options.

    • Use unit of underlying measures - uses the unit (Dollar, Euro, Yen, and so on) of the underlying measures.

    • Use number formatting of Account dimension - in tables, sets the calculation to use the same number formatting as is used in the Account dimension.

      Setting this option will also set the Use unit of underlying measures option if it is not already set.

  3. Select OK.

Related Information