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


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.

Environment Details

Product Feature: Field Derivations


  • No Downloads