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.

  • Click on the Add Aggregation button

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

  • Set the Grouping Table to People and the Transactional Table to Web Visits

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

  • 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

  • Enter an If( function followed by an And( function

  • Click on the Add Aggregation button

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

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

Part 2

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

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

  • Change the Transactional Table to Bookings

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

  • Drag the Destination variable onto the Pick the drop box

    Note: 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)

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

Part 3

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

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

  • Change the Transactional Table to Bookings

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

  • Change the From box to show Latest to Earliest

  • Drag the Destination variable onto the Pick the drop box

  • Rename this as "Last Booking Destination"

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

  • 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.

 

Click to return to Expressions: Aggregations on the Fly