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 a person's holidays have been ranked by Booking Date (see Rank Transaction aggregation example) , find the cost of the previous booking.

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

• Click on the Add Aggregation button

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

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

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

• Drag the Cost variable on to the Pick the drop box

• Click the Build button to see a preview of the results

A Data Grid can be used to validate the results:

 

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.

 

Click to return to Expressions: Aggregations on the Fly