Moving Average

info

This is part of our beta expression language AQL. Learn more. Request beta.

Knowledge Checkpoint

A grasp of these concepts will help you understand this documentation better:

• trailing_period

Introduction

Moving Average is a powerful analytical tool used to analyze time series data by smoothing out fluctuations and identifying trends. By calculating the average value of a variable over a rolling window of time, Moving Average helps to smooth out the variations and helps the actual trend stand out better.

In this tutorial, we will create a report that visualizes both the monthly revenue and the 3-month-moving average revenue of an E-commerce business using the trailing_period() function in Holisitcs.

Setup

In this guide, we will use the familiar e_commerce dataset:

Initial Setup

// orders.model.aml
Model orders {
  ...
  dimension id {...}
  dimension user_id {...}
  dimension created_at {...}
}

// order_items.model.aml
Model order_items.model.aml {
  ...
  dimension order_id {...}
  dimension product_id {...}
  dimension quantity {...}
}

// products.model.aml
Model products {
  ...
  dimension id {...}
  dimension price {...}
  dimension categor_id {...}
}

// e_commerce.dataset.aml
Dataset e_commerce {
  ...
  models: [orders, order_items, products]
  relationships: [
    relationship(order_items.order_id > orders.id, true),
    relationship(order_items.product_id > products.id, true)
  ]
}

Final Setup

// orders.model.aml
Model orders {
  ...
  dimension id {...}
  dimension user_id {...}
  dimension created_at {...}
}

// order_items.model.aml
Model order_items.model.aml {
  ...
  dimension order_id {...}
  dimension product_id {...}
  dimension quantity {...}
}

// products.model.aml
Model products {
  ...
  dimension id {...}
  dimension price {...}
  dimension categor_id {...}
}

// e_commerce.dataset.aml
Dataset e_commerce {
  ...
  models: [orders, order_items, products]
  relationships: [
    relationship(order_items.order_id > orders.id, true),
    relationship(order_items.product_id > products.id, true)
  ]

  metric revenue {
    label: 'Revenue'
    type: 'number'
    definition: @aql coalesce(order_items | sum(order_items.quantity * ecommerce_products.price), 0);;
  }

  metric moving_avg_revenue_3m {
    label: 'Moving Avg. Revenue (3 months)'
    type: 'number'
    definition: @aql trailing_period(revenue, ecommerce_orders.created_at, interval(3 months)) / 3;;
  }
}

High-level Flow

1. Create a normal aggregation metric: We will create a revenue metric in the dataset which will be used later in the definition of the moving metric.
2. Create a moving average metric: Using the trailing_period() function, we will turn the revenue metric into a moving aggregation that sums revenues over intervals of 3 months, and then divide the sum by 3 to obtain the average value.
3. Visualize

Implementation

1. Create a normal aggregation metric

In the definition file of the e_commerce dataset, we create a revenue metric that combines fields from the two models order_items and products :

Dataset e_commerce {
  ...

  metric revenue {
    label: 'Revenue'
    type: 'number'
    definition: @aql order_items | sum(order_items.quantity * products.price) ;;
  }
}

2. Create a moving average metric

We will use the trailing_period() function to make the revenue metric to aggregate sales over an interval of 3 months. Here we will need to decide two things:

• The field that serves as the time dimension: Since revenue is an information about orders, we choose the field orders.created_at. Note that there is no need to convert the field to the month grain - the time grain will be decided in the interval argument.
• The trailing interval: we use interval(3 months) to specify that revenue will be aggregated for every 3-month period.

Dataset e_commerce {
  ...

  metric revenue {
    label: 'Revenue'
    type: 'number'
    definition: @aql order_items | sum(order_items.quantity * products.price) ;;
  }

  metric moving_avg_revenue_3m {
    label: 'Moving Avg. Revenue (3 months)'
    type: 'number'
    definition: @aql trailing_period(revenue, orders.created_at, interval(3 months)) / 3;;
  }
}

The result:

The moving average value of the first two rows is low because we only take 1 and 2 months’ revenue and divide by 3. In visualization, we can filter them out if it makes more sense for our report.

3. Visualize

As we can see from the line chart, the moving average line is much smoother than the actual revenue line:

In real-life situations where the month-by-month sales may fluctuate more wildly, moving averages will be a valuable tool for analysts to better display trends.