Data Calculation Options

For each axis in the chart, you can specify how the value of the variable will be calculated.

The value can be defined in the following ways:

• For numeric variables (Money, Number, Quantity data types): sum, per unit, percent, contribution, formula;
  For Bar&Line, Time Series and Detailed Time Series charts there are also index, per unit index, cumulative sum and cumulative contribution.
• For string variables (Text data type): count, count distinct.

In the following example, we have three possible values of the Customer Type variable (Restaurant, Industry, Consumer) and there are nine records in the Invoice Price field, three for each of the Customer Type variable values:

Using this sample data, we will demonstrate what the available calculation options actually calculate.

Sum

Sum () calculates the sum of the selected data field (Datamart column).

The sum function returns a simple total of all records of the field:

Per Unit

Per unit () divides the sum of the selected data field by the sum of the Quantity field. The Quantity field is defined in the Datamart (Analytics > Datamarts; the data type of the field must be Quantity, the Function must be set to Per Unit Basis).

The per unit function takes the sum of the invoice price for each customer type and divides it by the amount of units purchased. The result is the price per unit:

Percent

Percent (%) calculates the percentage value of the selected data field in relation to the Percent Basis field. The Percent Basis field is set in the Datamart (Analytics > Datamarts; set the Function to Percent Basis).

The percent function takes the sum of the invoice price and returns its ratio to the sum of the list price (which is the percent basis field) as a percentage:

Contribution

Contribution () calculates the percentage value of the sum of the selected data field and displays it as a decimal.

The contribution function takes the sum of the invoice price for each customer type and calculates its proportionate part of the Invoice Price total:

Index

Index () takes the first record as a benchmark and displays all other values as a ratio to this benchmark. It enables you, for example, to see the variation in value over time.

Per Unit Index

Per unit index () is similar to the index function. It calculates the index of the sum of the selected data field divided by the sum of the Quantity field.

Cumulative Sum

Cumulative sum () calculates the total sum of data as it grows over time (or any other series or progression).

Cumulative Contribution

Cumulative contribution () calculates the percentage value of the sum of the selected data field and displays it as a cumulative sum of decimals.

Formula

Formula () allows you to use an Analytics calculation logic to manipulate the value of the selected data field.

Select the logic you want to use from the Formula drop-down list on the Series tab. Note that the name of the element in the calculation logic must match the name of the data field that you want to manipulate.

For more information, see Calculation Logic in Analytics.

Editor

For each of the options for numeric variables, you can define a mathematic formula for calculating the values. Click 'Editor' and the magnifying glass icon to open the Formula Builder. In the Command Templates menu, you will find all the available functions and Datamart fields which can be used in the expression.

Example

The following formula computes the gross profit given that you have the variables 'Cost' and 'Revenue' in your Datamart and you have previously selected 'Revenue' as the measure:

Sum({field} - Cost)

If you want to use some of the available functions, there must always be an aggregation in the formula:

if(sum({field})>10,10,0)

or

sum(if({field}<5,0,10))

Functions available in the Formula Builder:

• Min – Returns the minimum value of the expression result.
• Max – Returns the maximum value of the expression result.
• Avg – Returns the average value of the expression result.
• Sum – Returns the sum of the expression result.
• Count – Returns the count of the expression result.
• Count distinct values – Returns the distinct count of the expression result.
• Discrete percentile – Returns the given percentile in the discrete distribution.
• Continuous percentile – Returns the given percentile in the continuous distribution.
• Correlation coefficient
• Population covariance
• Sample covariance
• Population standard deviation
• Sample standard deviation
• Population variance – Returns the population variance of the input values (calculated as square of the population standard deviation).
• Sample variance – Returns the sample variance of the input values (calculated as square of the sample standard deviation).
• Average of the independent variable: (Σ(X)/N)
• Average of the dependent variable: (Σ(Y)/N)
• Number of input rows in which both X and Y are non-null
• Y-intercept of the (X, Y) linear regression
• R2: square of the (X, Y) linear regression correlation coefficient
• Coefficient (slope) of the (Y, X) linear regression
• Sum of squares of the independent variable: Σ(X - (X/N))²
• Sum of products of independent times dependent variable: Σ(X*Y) - Σ(X) * Σ(Y)/N
• Sum of squares of the dependent variable: Σ(Y - (Y/N))²
• Is null? – Checks whether the expression is null.
• If – Evaluates a Boolean expression and defines values that will be returned for both true and false result.
• If null value – Checks whether the expression is null and provides a replacement value.
• Convert to string – Converts the result of the expression to string.

Count

Count (#) returns the number of records (table rows) of the selected data field.

Count Distinct

Count Distinct () returns the number of unique (distinct) records.