Resolve issue where the max() derivation function is not giving the maximum value for each customer in Spectrum/Portrait Miner

Product Feature: Field Derivations


When trying to derive the max(Amount) by CustID, it does not give the maximum Amount for every CustID.


The max() function is an aggregation function.  Aggregation functions calculate a value either for the total population or broken down by a segmentation of the population. The segments are typically defined by the binnings in place on a small set of fields.

In this example:

    max(Amount) by CustID

the Amount value returned is the maximum for each binning of the CustID field.  This is easiest seen in the Crosstab Viewer:

maximum Amount crosstab


UPDATED: August 1, 2017
To get the required result, process the focus with the qsmeasure data build command, using the CustID as the key field, with a TML file containing a single derivation:

   create MaxAmount := max(Amount);

This process requires the focus to be sorted by CustID.

The resulting focus, containing the MaxAmount field, can be joined back onto the original focus if required.