Expressions: Aggregations On The Fly - Recency and Relative with Text Variable

It is possible to use Text variables on elements of the Recency and Relative options within ‘on the fly’ aggregations. More specifically:

• Recency select Nth

• Recency select Nth Distinct

• Relative

Text variables are not, however, supported in the same way as other variable types used for ‘on the fly’ aggregations because the result of the calculation returns an integer. This is a lookup number into the data file records for that particular item variable which can then be turned into text using a new Expression function:

VarValueText(variable reference, lookup number)

This will look up the relevant text value from that particular variable.

Let’s consider an example in practice:

Open a new Expression window and click on the Add Aggregation icon

To access the ‘on the fly’ options:

Click onto the Frequency(Bookings) tab
Select Recency from the Type drop-down menu options
Set the Grouping Table to People and the Transactional Table to Bookings
Order records by Booking Date from Earliest to Latest
Drag the Destination_Product text variable onto the Pick the drop-box

The Data Type at the top of the window indicates that this will create an Integer. To convert this to text:

Switch to the Expression tab and add the function

Note: The variable reference should be used (here “bo1CE2X2”). You can see this when you expand the System explorer window:

Use a Data Grid to verify the results:

Open a new Data Grid
Add Destination, Product and Booking Date to the display, group by Person URN and Sort Ascending by Booking Date
Name the Expression and drag and drop onto the Data Grid display
Build

This feature can also be used for Recency select Nth Distinct – simply check the Distinct box in the Recency set-up window:

You can also use the Relative option to find, for example, what is my next Destination_Product transaction.

Click to return to Expressions: Aggregations on the Fly