Skip to Content
SAP Business ByDesign

Creating Custom Calculated Key Figures - Analytics

Tags:

A calculated Key Figure is a key figure that is determined using calculation rules or formulas.

You can create a calculated key figure using existing key figures in the data source that you've selected.

For example, you can create a formula to determine the number of customers for which zero activity has been recorderd: EQ(Number of Customers, 0)

Creating the Calculated Key Figure

In order to create a custom Calculated Key Figure, please, check the steps below:

  1. Go to the Business Analytics work center and access the view Design Key Figures;
  2. On the Design Key Figure screen click on New;
  3. On the New Key Figure screen, define a name and a description for your key figure, then, select a data source to be used, this data source will store your newly created key figure, and the key figure will be available in all reports that are based on this data source;
  4. On the Type selection, select Calculated Key Figure;
  5. On the new step of the Key Figure creation, Restrict/Calculate Key Figure *, you will see that all Key Figures existing in the data source, except the ones that are part of a specific Key Figure Structure, will be shown in the left area of the screen, while in the right part of the screen you will see the box to type in the formulas to be used in the new Key Figure, later on this document, I will cover all operations available for Formulas.
  6. After defining which Formula you want to use, click on the button Next;
  7. In the third step of the Key Figure creation, Properties, you will have a few fields to select, such as Calculate Result As and Conversion Type, these fields will also be covered later on this document. After setting what you want to use for each field (they can also be left blank, however results might differ) you can click on Next to review your created Key Figure, or you can click on Finish to save your key figure and make it available for reporting.

Formulas

Follow below a table with all available operations and their descriptions with use case.

Basic Operators
OperatorDescriptionUse Case
Add ( + )Calculates the sum of operand 1 and operand 2.

"KF1" + "KF2" or n1 + n2

Subtract ( - )Subtracts operand 2 from operand 1."KF1" - "KF1" or n1 - n2
Multiply ( * )

Calculates the product of operand 1 and operand 2.

"KF1" * "KF2" or n1 * n2
Divide ( / )Divides operand 1 by operand 2."KF1" / "KF2" or n1 / n2
Power ( ^ )Returns the value of operand 1 raised to the power operand 2."KF1" ^ "KF2" or n1 ^ n2
Percentage Deviation ( % )Calculates the percentage deviation between operand 1 and operand 2."KF1" % "KF2" or n1 % n2
Percentage Share ( %A )Calculates the percentage share of operand 1 and operand 2."KF1" %A "KF2" or n1 %A n2
ParenthesesGroups and sets the order of operators.("KF1" + "KF2") * KF3 or (n1 + n2) * n3 - Can be used multiple times if required.
CommaDenotes a series in a list.KF1, KF2, KF3 or n1, n2, n3
Boolean Operators
OperatorDescriptionUse Case
EQEqual ToEQ("KF1", 0)
GEGreater Than or Equal ToGE("KF1", 0)
GTGrater ThanGT("KF1", 0)
LELess Than or Equal ToLE("KF1", 0)
LTLess ThanLT("KF1", 0)
NENot Equal ToNE("KF1", 0)
ANDLogical AND - The result is 1 if both operand 1 and operand 2 do not equal 0. Otherwise, the result is 0.AND("KF1", "KF2")
NOTLogical NOT - The result is 1 if the operand is 0. Otherwise, the result is 0.NOT("KF1")
ORLogical OR - The result is 1 if either the operand 1 or operand 2 does not equal 0. Otherwise, the result is 0.OR("KF1", "KF2")
XORLogical exclusive XOR - The result is 1 if either operand 1 or operand 2 (but not both) does not equal 0. Otherwise, the result is 0.XOR("KF1", "KF1")
LEAFValues in Hierarchy - The result is 0 for results rows or real (inner) nodes of a hierarchy, and the value 1 for elementary rows or the leaves of a hierarchy.  This operator allows you to carry out various calculations on results rows and elementary rows.LEAF("KF1")
Mathematical Functions
ABSAbsolute Value - Returns the value of the operand irrespective of its sign (+/-)ABS("KF1")
SIGNSign of Operand - Returns the value of the operand along with its sign (+/-)SIGN("KF1")
CEILCeiling - Returns the next smallest integer value that is greater than the operand.CEIL("KF1")
DIVDivision - Integer division operator: for example, 6 DIV 4 returns 1.DIV("KF1", "KF2")
EXPExponential - An operand is raised to a variable power.EXP("KF1")
FLOORFloor - Returns the next greatest integer value that is less than the operand.FLOOR("KF1")
FRACFraction - Returns decimal part of the division of operand 1 and operand 2.FRAC("KF1", "KF2")
LOGLogarithm - Returns the natural logarithm of X.LOG("KF1")
LOG10Base 10 Logarithm - Returns the logarithm to the base 10 of X.LOF10("KF1")
MAXMaximum - Returns the greatest value in a range.MAX("KF1", "KF2")
MAX0Maximum of 0 and the operand.MAX0("KF1")
MINMinimum - Returns the smalles value in a range.MIN("KF1", "KF2")
MIN0Minimum of 0 and the operand.MIN0("KF1")
MODRemainder - Integer remainder operator. For example: 6 MOD 4 returns 0.5.MOD("KF1", "KF2")
SQRTSquare Root - Returns the square root.SQRT("KF1")
TRUNCTruncate - Returns the integer part of X.TRUNC("KF1")
Data Functions
COUNTCount - Value equals 1 if operand is 0. Otherwise, the result is 0.COUNT("KF1")
NDIV0Divide by Zero - Equals 0 when divided by 0. Otherwise, the result is the value of the operand.NDIV0("KF1")
NOERRZero if Error - Equals 0 if the calculation of operand leads to an arithmetical error. Otherwise, the result is the value of the operand.NOERR(Operation)
%CTPercentage Share of Result - Shows results as a percentage share of the overall result."KF1" %CT "KF2"
%GTPercentage Share of Overall Result - Shows values as a percentage share of the overall result."KF1" %GT "KF2"
%RTPercentage Share of Report Result - Calculates a result similar to (%GT)."KF1" %RT "KF2"
DATEValue as Date - Returns the integer value in date format.DATE("KF1")
NODIMNo Unit or Currency - Returns numeric values of the operand and suppresses units and currencies.NODIM(Operation)
SUMCTSum of Result - Returns the result of the operands to all rows or columns.SUMCT("KF1")
SUMGTSum of Overall Result - Returns the overall results of the operands.SUMGT("KF1")
SUMRTSum of Report Result - Returns the report result of the operand.SUMRT("KF1")
TIMEValue As Time - Returns the integer value in time format. The system cuts off the decimal places, interprets the value as seconds, and displays the value in the form +-hh:mm:ss. For example, 4812 is displayed as 1:20:12.TIME("KF1")
DELTADelta - Returns the value 1 if the expression is 0, else 1.DELTA(Operation)
Trigonometric Functions
ACOSArc CosineACOS("KF1")
ASINArc SineASIN("KF1")
ATANArc TangentATAN("KF1")
COSCosineCOS("KF1")
COSHHyperbolic CosineCOSH("KF1")
SINSineSIN("KF1")
SINHHyperbolic SineSINH("KF1")
TANTangentTAN("KF1")
TANHHyperbolic TangentTANH("KF1")

Key Figure Properties

These properties are to be used in the step 3 of the key figure creation. Please, check the table below for information on each of the types for Calculate Result As, Calculate Single Value As and Aggregation Type. For further information, please, check the tables below.

Calculate Result and Single Value As

Administrators can recalculate single values and results of a report based on certain criteria. For example, you can create ranked lists, or you can calculate the total for a Top 10 product list locally.

ValueDescription
Counting All Detailed ValuesAll values for a characteristic are counted and numbered.
Counting All Detailed Values That Are Not Zero, Null, or ErrorExcluding the values that are equal to zero, all values for a characteristic are counted and numbered.
First ValueThe highest value or the value furthest to the left of the corresponding area is displayed as the result. *
HideThe result is not displayed.
Last ValueThe lowest value or the value furthest to the right of the corresponding area is displayed as the result. *
MaximumThe largest value for the corresponding area is displayed as the result.
MinimumThe smallest value for the corresponding area is displayed as the result.
Moving Average The system calculates the average of all values.
Moving Average For All Values That Are Not Zero, Null, or ErrorExcluding the values that are equal to zero, the system calculates the average of all values.
Normalize According to Next Group Level Result

The data is displayed as a percentage of the result of the next group level. The values of the results row and the overall results row are not displayed as percentages but as absolute values.

If there is only one characteristic in the drilldown, the result of a group level is the same as the overall result. **

Normalize According to Overall Result

The data is displayed as a percentage of the overall result. The values of the results row and the overall results row are not displayed as percentages but as absolute values.

If there are multiple characteristics in the drilldown, there are different results, which are combined to form an overall result. **

Olympic Rank Number

The olympic ranked list differs from the basic ranked list as follows: In the olympic ranked list, when a value occurs more than once, the next smallest value is not assigned the rank incremented by one, but the rank that corresponds to the number of previous characteristic values (including the current value).

For example, there are three products with a higher rank than product D, therefore D has rank 4 and rank 3 is not assigned since B and C have the same rank (2). **

Rank Number

The characteristic values are sorted according to the selected structure element and are given a ranking. The order of the ranked list is based on the size of the value for the structure element, where the largest value has rank 1 and the smallest value has the last rank. 

If a value occurs more than once, the corresponding characteristic values are assigned the same rank. In a basic ranked list, the next smallest value is assigned this rank incremented by one. **

Standard DeviationStatistical deviation is a measure of the distribution of the values with respect to the mean value (average). Statistical deviation = root from the variance: SQRT (variance). *
Summation of Rounded ValuesIf you have set a scaling factor, it is useful to calculate the total sum of rounded values locally since the total sums can differ considerably (in particular with high scaling factors). *
TotalThe sum total of the values for the corresponding area is displayed as the result. *
VarianceVariance is a measure of the distribution of the values with respect to the mean value (average).

* Denotes the options that are valid only for Calulate Result As.

** Denotes the options that are valid only for Calculate Single Value As.

Except Aggreation

Administrators can define exception aggregation behavior for key figure values when they create a calculated key figure.

The deviation to the standard aggregation behavior is only valid in combination with a specified characteristic.

ValueDescription
AverageThe average of all values is displayed.
Average of Detailed Values That Are Not Zero, Null, or ErrorThe average of the column value not equal to zero is displayed in the results row.
Average Weighted with Calendar DaysThe average of the column value weighted with the number of days is displayed in the results row.
Average Weighted with Working DaysThe average of the column value weighted with the number of workdays is displayed in the results row.
Counter for All Detailed ValuesThe number of existing values is displayed in the results row.
Counter for All Detailed Values That Are Not Zero, Null, or ErrorThe number of values <> zero is displayed in the results row.
Exception If More Than One Record OccursNo aggregation
Exception If More Than One Value <> OccursNo aggregation
Exception If More Than One Value OccursNo aggregation
First ValueThe first value in relation to the reference characteristic is displayed in the results row.
Last ValueThe last value in relation to the reference characteristic is displayed in the results row.
MaximumThe maximum value of all values displayed in this column is displayed in the results row.
MinimumThe minimum value of all values displayed in this column is displayed in the results row.
No Aggregation Along Hierarchy
No Aggregation of Posted Nodes Along Hierarchy
Standard DeviationThe standard deviation of the displayed values is displayed in the results row.
TotalThe sum of all values displayed in this column is displayed in the results row.
VarianceThe variance of the displayed values is displayed in the results row.

In case you have more queries about Analytics issues, please, check the Central Document for Analytics Troubleshooting.