Expressions: Aggregations on the fly - Recency

Date and DateTime variables can be applied in Recency aggregations.

Text variables are also supported where relevant - see Expressions: Aggregations on the Fly - Recency and Relative with Text Variable for a worked example.

Scenario 1:

Identify the duration of a person's last web visit.

  1. Click on the Add Aggregation button.

  2. Click on the tab entitled FrequencyBookings and change the Type box to show Recency.

  3. Set the Grouping Table to People and the Transactional Table to Web Visits.

  4. Change the From option to Latest to Earliest and leave the Select WebVisit number as 1 to pick the latest one.

  5. Drag Duration onto the Pick the drop box as your item variable.

A Data Grid can be used to validate the results:

Scenario 2:

Identify people who have made more than 2 bookings where the destination of their first and last booking is the same.

As the screen shot suggests, there are 3 aggregations making up the overall expression here:

Part 1

  1. Enter an If( function followed by an And( function.

  2. Click on the Add Aggregation button.

  3. Click on the Frequency(Bookings) tab and change the Transactional Table to Bookings.

  4. Return to the Expression tab and enter >2 after the Frequency(Bookings} statement followed by a comma.

Part 2

  1. Enter the DescOf( function and then click on the Add Aggregation button.

  2. Click on the Frequency(Communications) tab and change the Type box to Recency.

  3. Change the Transactional Table to Bookings.

  4. Drag the Booking Date variable on to the Order records by drop box.

  5. Drag the Destination variable onto the Pick the drop box.

Whilst a default Name is allocated to each aggregation within the overall expression, these can be easily over-typed, if required - e.g. We could re-label this as "First Booking Destination" (see screenshot below)

  1. In the expression window enter a closed bracket ) followed by the equals symbol =

Part 3

  1. Enter the DescOf( function and then click on the Add Aggregation button.

  2. Click on the new Frequency(Communications) tab and change the Type box to Recency.

  3. Change the Transactional Table to Bookings.

  4. Drag the Booking Date variable on to the Order records by drop box.

  5. Change the From box to show Latest to Earliest.

  6. Drag the Destination variable onto the Pick the drop box.

  7. Rename this as "Last Booking Destination".

  8. In the expression window enter the following after {Last Booking Destination} )),1,0).

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

A Data Grid can be used to validate the results.

Scenario 3:

Recency Select Nth and Select Nth Distinct 'on the fly' aggregations can support textual items. Note that these aggregations return an integer and, as a result, it is necessary to use the Expression function VarValueText to look up a relevant text value from the variable. The example below identifies the first booking tweet a person made, and the Expression ensures that we can view the result as text.

 

Added in Q3 23

There is the option to add a second ordering variable which allows you to break ties when the values of the first ordering variable are equal. For example, you may want to identify the cost of a person's most expensive transaction when that person has multiple transactions on the same date. The example below demonstrates Booking Date from Latest to Earliest as the first ordering variable and Cost from Largest to Smallest as the second. Viewed on a data grid you can see that:

  • This person's last two transactions were made on 27-09-2021 with a tie between a booking to the United States and another to Australia.

  • Without the optional ordering, either of the tied transactions could be returned - here it is the transaction costing £180.85 to Australia.

  • With the tie broken by the most expensive booking, the transaction costing £248.87 to the United States is returned.

 

Click to return to Expressions: Aggregations on the Fly