Create Calculated Dimensions for Tables

In your SAP Analytics Cloud table, create a calculated dimension when you want to concatenate dimensions together, group dimensions, create a measure-based dimension, and so on.

You can choose to combine existing dimensions to create your own dimensions.

Note

The number format chosen in the user preferences ( Start of the navigation path

Profile Settings Next navigation step

User Preferences End 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.

Create one of the following calculated dimensions to use in your tables:

Concatenated dimension
Grouped dimension
Measure-based dimension

Functions, Conditions, and Operators

The following functions, conditions, and operators are available for creating calculated dimensions. For descriptions of each option, see All Formulas and Calculations.

Functions:

`IF()`	`NOT()`	`ISNULL()`	`LIKE()`	`TOTEXT()`	`TONUMBER()`
`LENGTH()`	`CONCAT()`	`SPLIT()`	`SUBSTRING()`	`TRIM()`	`ENDSWITH()`
`FINDINDEX()`	`REPLACE()`	`LEFT()`	`RIGHT()`	`LOWERCASE()`	`UPPERCASE()`

Note

The ISNULL function identifies NULL values, but won't replace a NULL value with a value.

For example, your calculated dimension has the following formula: CD1=IF(ISNULL(D1), "No Value", D1)

The value in cell D1 will not be changed to show the words “No Value”: it will stay as a NULL value.

Conditions:

AND	OR
> (greater than)	< (less than)
>= (greater than or equal)	<= (less than or equal)
= (equal)	!= (not equal)

Operators:

+ (addition)
- (subtraction)
* (multiplication)
/ (division)

How to Set Up a Calculated Dimension

Use the Calculation Editor to set up your calculated dimension.

In Builder, under Rows or Columns, select Add Dimensions Calculated Dimensions Create Calculated Dimension.
In the Calculation Editor under Type, select Calculated Dimension.
Provide a name for your calculated dimension.

Remember

You can press Ctrl + Space in the formula area to display a list of suggestions, or type [ for a list of valid measures and dimensions.

How to Create a Concatenated Dimension

To create a concatenated dimension, do the following:

In the Edit Formula area, type your formula.

To concatenate two or more dimensions, use a plus (+) between the values. You can add a space as well:

Example
[Product] + " " + [Region]
To close the Calculation Editor, click OK.

The calculated dimension is ready to be used in your table.

How to Create a Grouped Dimension

You can choose to create your own member groupings for your data. For example, you may want to group sales from specific cities into North America, Europe, and Other regions. Using the Calculation Editor, create your own If, then, else formula.

In the Edit Formula area, type an IF statement with the specific conditions.

Example
IF ([city] = ("Vancouver", "Duluth", "Tijuana"), "North America", IF ([city] = ("Berlin", "Paris"), "Europe", "Other"))
To close the Calculation Editor, click OK.

The calculated dimension is ready to be used in your table.

How to Create a Measure-Based Dimension

You can create a calculated dimension based on measure or account values or thresholds. For example, you could create a high/medium/low table row or column based on your own criteria.

To create a measure-based dimension, do the following:

In Builder, under Rows or Columns, select Add Dimensions Calculated Dimensions Create Calculated Dimension.
In the Calculation Editor under Type, depending on whether you're working with an classic account model or a new model type, select Measure-Based Dimension or Value-Based Dimension.
Provide a Name for the dimension.
In the Properties section, select an account, a measure, or both using the dedicated fields:
- If you’re working with a new model type, select the measure and the account using the dedicated field.
- If you're working with a classic account model, select a measure.
Select Use measure values as dimension members.

This will replace the member name field. You can set the scale and the number of decimal places for the measure values that are converted to dimension members.
Provide a member name: for example, high, medium, or low.
Set the Measure Values.
From the Dimension Context, select one or more versions.

Note
For hierarchical dimensions, you also need to specify the level. For example, Date.Year
(Optional) In Filter Context, add story or page filters.

Story and page filters are not automatically applied to measure-based dimensions, which means that some filtering results may not be as expected. This is easy to see when you use measure values as dimension members.
(Optional) In Filter Context, add a specific static filter.

For example, you can use the Date dimension and select a single value such as a year or quarter.
(Optional) In Filter Context, add a Calculation Input Control.
1. Select a dimension.
2. From Values or Input Controls, select Create a New Calculation Input Control.
3. Provide a name for the input control.
4. In the Input Values area, select your values.
5. When finished, select OK.
When finished, select OK.

The calculated dimension is ready to be used in your table.