Skip to content

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

You can optionally group a person's transactions by the categories within a selector variable - see example below.

To get started with creating running and rolling aggregations, open a new expression window:

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

  2. From the Type drop down select Running/Rolling.

  3. Select the appropriate Transactional and Grouping tables – in the following examples, Bookings and People.

Running

  1. Order Records by Booking Date from Earliest to Latest.
  2. Pick the Cost.

  3. Ensure the Running radio button is checked.

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

  4. Select Running Sum.

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

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

  1. Order Records by Booking Date from Earliest to Latest.
  2. Pick the Cost.

  3. Ensure the Rolling radio button is checked.

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

  4. Select Rolling Sum.

  5. Enter a Window Size of 3 so that our rolling sum is across 3 transactions.
  6. 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.

With the rolling time window option you can, for example, determine the highest amount spent by a customer in any 12 month period.

Rolling count

It is possible to create a rolling count of transactions on a time window going backwards. This allows you to answer questions such as 'from this transaction, how many transactions were there in the previous N days, weeks, months, quarters or years.

The rolling count option is only available when:

  • No item variable is selected
  • Rolling is selected

  • You set a time window

The following screenshot displays the rolling counts of transactions over 6, 12 and 18 months:

Using a grouping variable

Running and rolling aggregations allow you to optionally group a person's transactions by the categories within a selector variable. For example, to display the running total cost spent by an individual, grouped by the different destinations they have visited:

  1. Create a new Running/Rolling aggregation.
  2. Select the appropriate Transactional and Grouping tables – in this example, Bookings and People.
  3. Drag and drop Destination as the selector variable to group by.
  4. Order records by Booking Date from Earliest to Latest.
  5. Pick the Cost.

  6. Select Running and Running Sum as the function.

You can view the results on a data grid:

Grouping is supported for running, rolling across a fixed number of transactions, and rolling across a time period. The results within each group start from the first transaction.