Create Calculated Accounts for Charts

In SAP Analytics Cloud, the Calculated Account calculation creates a new account that you can use in your chart.

Context

Accounts, are numerical values on which you can use mathematical functions. When setting up your calculation, you’ll apply the typical formula functions, conditions, and operators to the data contained in your model.

Remember

Depending on your model or data source, you may see either accounts or measures or both.

Calculated accounts allow you to perform mathematical and Boolean operations on your data. For example, you can use a calculated account to chart the effect a sales increase of 20% would have on profits.

Note

The number format chosen in 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, Calculated Accounts, 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.

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

Functions

`IF()`	`POWER()`	`NOT()`
`ABS()`	`GrandTotal()`	`TRIM()`
`LOG()`	`%GrandTotal()`	`FINDINDEX()`
`LOG10()`	`LENGTH()`	`RIGHT()`
`INT()`	`LIKE()`	`LEFT()`
`FLOAT()`	`SUBSTRING()`	`SPLIT()`
`DOUBLE()`	`ISNULL()`	`ENDSWITH()`

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)

Tip

In the Calculation Editor, you only see the functions that are valid for your data source.

Restriction

Charts can use only one Account Hierarchy at a time. You can use different hierarchies for different charts, but if you change an account hierarchy in a chart, your data may no longer be displayed.

Procedure

Select a chart.
Open the Calculation Editor: in Builder, choose Add Account Calculations Create Calculation.

Note
The option to create a new calculation may not appear if calculations are not possible for the chart type or model.

The Calculation Editor appears.
Select Calculated Account from the list.
Enter a name for the calculated account.
Enter a formula in the Edit Formula area.
Tip

As you are adding formula details, you will see a message appear and disappear: it appears when your formula is not valid and disappears when the formula is valid.

Existing input controls appear in the Available Objects list, and can be added to a formula.

Note
Only single value numeric input controls can be used in formulas.

You can add preset functions, conditions, and operators, by selecting options in the Formula Functions list. You can use IF conditional functions, and you can display a list of possible formulas for the function by pressing Ctrl + Space bar.
The formula editor supports auto-complete and the following shortcuts:
- [ – Returns all relevant accounts:
  - Adding a calculation to the Account dimension: shows Account dimension members.
- # – Returns all calculations (that is, measures created using the Calculation Editor).
- @ – Returns input controls. (Only single value numeric input controls are returned.)
Tip

In the list of accounts, you see both the account ID and the account description. After you select an account, the formula editor area shows only the account ID. To view the account description, click outside the formula editor area.
(Optional) Create a calculation input control.
1. Under INPUT CONTROLS, select Create New.
2. Enter a name for the input control.
3. Select Existing Dimension to allow users to pick from members of a dimension, or Static List to add custom values as options for the input control.
  - Existing Dimension
    
    Select a model, select a dimension, and then select Click to Add Values.
    
    Select values from the list of available members.
    
    If you select Exclude selected members, all members except the ones selected will be included in the input control. You can use (Search) to find specific values. When you expand the list beside the search icon, you can choose to view the member Description, ID and Description, or ID.
    
    Expand the Settings for Users section, and then choose whether users can do the following in the input control: Single Selection, Multiple Selection, or Multiple Selection Hierarchy.
    
    Select OK.
  - Static List
    
    Select Click to Add Valuesand choose either Select by Range or Select by Member.
    Note
    
    Choose Select by Range to specify a range for a numeric slider. Choose Select by Member for an input control based on a defined set of members.
    
    The Select by Range option appears only if dimension values are numerical or date based. If the dimension is date based, you can also select quarter, month, or year from the slider that appears. Ranges can be fixed or dynamic; for example, you could choose the fixed range January 2017 to December 2017. If this story is opened in 2018, the story will still show 2017 data. For dynamic date ranges, in addition to the above granularities, these granularities are also available: current year, current quarter, and current month. For more information, see Story and Page Filters.
    
    To create a numeric slider, enter the Min and Max values for your range in the Set Values for Custom Range dialog. You can optionally set an Increment value for the slider.
    
    Select OK.
    
    To create a member based input control, add numeric values to the Custom Members area in the Select Values from Custom LOV dialog, and then select Update Selected Members.
    
    Expand the Settings for Users section, and then choose whether users can do the following in the input control: Single Selection, or Multiple Selection.
    
    Select OK.
4. Select OK.
  
  The input control you created appears under Available Objects.
5. Select the input control to add it to a formula.
Note
When you create a calculation input control, if you select “All Members”, a dynamic filter is created. This means that the latest dimension member descriptions are always fetched from the model. But if you select individual dimension members, a static filter is created. This means that the dimension member descriptions are remembered from the time when the input control was created. For details, see Story and Page Filters.
(Optional) If you want to verify that your formula is formatted correctly, select Format: it may reformat your formula before displaying a valid formula message.
Select OK.

Results

An account is created based on the formula you entered, and it is displayed as a new account dimension.

If the formula uses an input control, it will appear on the canvas.

On the canvas, input controls are indicated by the (Formula) icon. If you hover over the icon, all calculations associated with the input control are displayed. By default, the input control is displayed in token mode where input values can be selected from a drop-down list. The input control can be expanded into widget mode, where radio buttons appear beside each value.