Moving Average
This is part of our beta expression language AQL. Learn more. Request beta.
A grasp of these concepts will help you understand this documentation better:
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
- 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)
]
}
// 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
- 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. - Create a moving average metric: Using the
trailing_period()
function, we will turn therevenue
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. - 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 themonth
grain - the time grain will be decided in the interval argument. - The trailing interval: we use
interval(3 months)
to specify thatrevenue
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.