Expressions: Aggregations on the Fly - Running and Rolling

The Running/Rolling functionality allows us to establish the value of a transaction from a defined number of previous transactions.

  • Running Aggregation – will work out a value for this transaction from ALL the transactions up to and including it

  • Rolling Aggregation – will work out a value for this transaction from a fixed number of transactions up to and including it

In a new Expression window:

  • Click on the Add Aggregation button and then on the tab that opens up

  • From the Type drop down select Running/Rolling

  • Select the appropriate Transactional and Grouping tables – in this example, Bookings and People

Running

  • Order Records by Booking Date from Earliest to Latest

  • Pick the Cost

  • Ensure the Running radio button is checked

The functions available are Running - Sum, Average, Minimum, Maximum.

  • Select Running Sum

If required, it is possible to add a transaction filter, rather than applying the aggregation across the entire database.

  • Name the expression Running Sum of Cost

We can sense check the expression using a Data Grid, grouped by Person URN and ordered into ascending order by Booking Date:

Rolling

  • Order Records by Booking Date from Earliest to Latest

  • Pick the Cost

  • Ensure the Rolling radio button is checked

The functions available are Rolling - Sum, Average, Minimum, Maximum.

  • Select Rolling Sum

  • Enter a Window Size of 3 so that our rolling sum is across 3 transactions.

  • Name the expression Rolling Sum of Cost

We can sense check the expression using a Data Grid, grouped by Person URN and ordered into ascending order by Booking Date:

When selecting the Rolling Average function, the Weight Type drop-down becomes accessible, with 3 options to control the weighting of how important the previous transactions are:

  • Simple (No Weighting) - applies no weighting

  • Linear Weighted – weights the transactional value over time, decreasing in a linear fashion, with the highest weighting being assigned to the most recent transaction

  • Exponential Weighted – weights the transactional value over time, decreasing exponentially, with the highest weighting being assigned to the most recent transaction

Linear and Exponential weighting are useful, giving higher importance to more recent transactions.

Added in Q4 21 - Running / Rolling aggregations now also support a rolling time window allowing you to determine, for example, the highest amount spent by a customer in any 12 month period.

 

Click to return to Expressions: Aggregations on the Fly