Calculations

Calculation Builder

As well as being able to create simple aggregations on numeric fields including SUM, AVG, MIN, MAX and MEDIAN, Analytics includes a facility to derive new calculations; either on the fly when a report or Crosstab is ran, or at an index build time for storing directly in the index.

Users and administrators are able to create custom calculations using the point and click Calculation Builder. The calculation menu allows you to create ‘pseudo fields’ and re-usable variables by using one or many of the preconfigured functions, CASE statements, IF statements or mathematical calculations.

The available functions are grouped into the following categories:

• Numeric Functions
• Date Functions
• String Functions
• Boolean Functions
• Aggregation Functions
• Formfield Calculations
• Miscellaneous Functions

Some of these functions are only available for use in Calculated Fields and not Crosstabs. Usage availability will be detailed against each function.

Distinct Counts

Calculations can be based on formulas driven off event level data or from one or more distinct counts. Distinct counts are the number of unique values contained in one or more key fields.

Example: Count the number of departments, count the number of people within a department, or count the total number of sales order lines.

Distinct counts are similar to SELECT COUNT (DISTINCT field name) in SQL. However, you are able to generate these counts simply by selecting the fields you wish to group and count from a drop list.

Another form of distinct count is the creation of Dynamic Views. Against an index an administrator can create any number of dynamic views, which are the non-repeating fields that relate to one or more unique keys held within an index.

Example: If an index contained transaction information relating to items purchased via credit card where each item purchased appears as a separate transaction, you could configure a dynamic view called “Credit Card Holder” which would allow you to analyse data in terms of “Distinct Credit Card Holders” as opposed to individual transactions. This view would allow you to restrict which fields are shown to the user so that they only relate to details about the credit card and holder, opposed to the individual transactions.