Expressions: Aggregations on the fly - Relative Transaction

Date and DateTime variables can be applied in Relative aggregations.

Text variables are also supported where relevant.

Scenario

When all of a person's holidays have been ranked by Booking Date (see Rank Transaction aggregation example) , find the cost of the previous booking.

  1. Open a new Expression window from Toolbox ribbon bar and set the table level to Bookings.

  2. Click on the Add Aggregation button.

  3. Click on the tab entitled Frequency(Bookings) and change the Type box to show Relative Transaction.

  4. Drag the Booking Date variable onto the Order records by drop box and change the order display to Latest to Earliest.

    From Q4 2023, you have the option to specify two ordering variables and use the second one to break ties when the first one is equal.

  5. Leave the Select Booking set to 1 to find the previous order as opposed to 2 to find the second previous order etc.

  6. Drag the Cost variable on to the Pick the drop box.

  7. Click the Build button to see a preview of the results.

A Data Grid can be used to validate the results:

Relative Transaction aggregation results are returned on the transaction table.

Relative (and Recency Select Nth and Select Nth Distinct) 'on the fly' aggregations can support textural items. These aggregations return an integer and, as a result, it is necessary to use the VarValueText Expression function to look up a relevant text value from the variable. A worked example can be see in Expressions: Aggregations On The Fly - Recency and Relative with Text Variable.

Added in Q2 2025

By default, when creating Relative on the fly aggregations, no de-duping of the results is carried out. However, there are two options to de-duplicate and remove repetitions :

  • Different - Next N returns the Nth record after this one once direct repeats are de-duplicated and removed.

  • Distinctly Different - Next N returns the Nth distinctly different record after this one once direct repeats are de-duplicated and removed

The screenshot below displays an individual's six holiday destinations visited and provides examples of the available options:

When N=1 the results for both different and distinctly different are the same, but once N>1, the impact of the de-dupe options can be seen.

Focusing on the above person's 1st transaction, the following screenshots examine the options and results based on searching for their 2nd next transaction.

2nd Next Destination - No deduping

2nd Next Destination - Different

2nd Next Destination - Distinctly Different

 

Click to return to Expressions: Aggregations on the Fly