How to aggregate data in Spectrum/Portrait Miner

Product Feature: Field Derivations, Data Build Utilities
Decision Studio is more usually used to analyze customer level data, that is data which may already have been aggregated to produce one record per customer.

Transaction data can be aggregated to produce customer level data by the qsmeasure Data Build Command (DBC), this functionality can also be accessed via the Transform - Aggregate Records right-click menu in Spectrum/Portrait Miner.

Transform - Aggregate Records menu

The qsmeasure DBC requires an aggregations file containing create statements, and a key field(s) to identify groups of records for aggregation.

In addition to this, Decision Studio contains a number of built-in aggregation functions which are available for use in abstract crosstabs.  A crosstab in this sense consists of zero or more breakdown fields, together with one or more statistics.  The statistics are evaluated for every cell of the potentially multi-dimensional segmentation that is defined by the binnings on the breakdown fields.

For example a simple crosstab in the Crosstab Viewer, displaying mean(CardVisa) for the Gender field:

Crosstab Viewer

The same aggregation can be performed as a field derivation in the Table Viewer, using the derivation text  mean(CardVisa) by Gender :

Table Viewer with aggregation derivation

A full list of aggregation functions for use in derivations is available in the Function Browser:

Function Browser tool
UPDATED:  August 2, 2017