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.

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 (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