Period Comparison
AQL techniques for comparing metrics across time periods in code, covering relative offsets, fixed date ranges, granularity control, and CAGR calculations.
Alternative
We also support native GUI-based time period comparison.
Introduction
Period Comparison is an analytic technique to compare metrics from different time periods, such as comparing the revenue between this year and last year. In this guide, we will walk you through detailed steps on how to calculate period comparison metrics in different approaches for different use cases
Setup
# e_commerce.dataset.aml
Dataset e_commerce {
models: [orders, countries]
relationships: [
relationship(orders.country_id > countries.id, true),
]
}
# orders.model.aml
...
# countries.model.aml
...
Method 1: Relative period
You may consider writing measure that relatively changes based on the present period or the conditioned period. This is useful when you want to compare a specific KPI from one period to another one relatively. In AQL, we can employ relative_period() to perform that result
# orders.model.aml
...
measure count_orders {
label: "Count Orders"
type: "number"
definition: @aql count(orders.id) ;;
}
measure count_orders_previous_year {
label: "Count Orders Previous Year"
type: "number"
// highlight-next-line
definition: @aql orders.count_orders | relative_period(orders.created_at, interval(-1 year)) ;;
}
measure percent_change_previous_year {
label: "Percent Change Previous Year"
type: "number"
definition: @aql safe_divide(
(orders.count_orders - orders.count_orders_previous_year)*1.0,
orders.count_orders_previous_year
);;
}Example 1
explore {
measures {
orders.count_orders,
orders.count_orders_previous_year,
orders.percent_change_previous_year
}
filters {
orders.created_at matches @(last year)
}
}| Count Orders | Count Orders Previous Year | Percent Change |
|---|---|---|
| 408 | 1102 | -0.62976406533575317604 |
Example 2
explore {
dimensions {
countries.continent_name
}
measures {
orders.count_orders,
orders.count_orders_previous_year,
orders.percent_change_previous_year
}
filters {
orders.created_at matches @(last year)
}
}| Continent | Count Orders | Count Orders Previous Year | Percent Change |
|---|---|---|---|
| America | 43 | 129 | -0.66666666666666666667 |
| Australia | 52 | 119 | -0.56302521008403361345 |
| Asia | 170 | 516 | -0.67054263565891472868 |
| Europe | 143 | 338 | -0.57692307692307692308 |
Method 2: Fixed period vs fixed period
Note that the filter condition applied at the exploration layer is combined with the measure condition using AND logic. Hence the measure condition may yield an inaccurate result if it doesn’t cover the entire exploration condition’s time range. E.g. count_orders_2023 will return no data if there is an exploration condition orders.created_at matches @(2024) applied
If you would like to use filter condition together with a defined period, check out the method 3 and method 4 below. It will provide a clearer understanding of how the filter condition and defined period interact
In case you prefer to maintain a fixed period within the measure definition. You can apply where() along with a time operator to filter the specific period from which you want to derive the measure
# orders.model.aml
...
measure count_orders_2023 {
label: "Count Orders in 2023"
type: "number"
// highlight-next-line
definition: @aql orders.count_orders | where(orders.created_at matches @2023) ;;
}
measure count_orders_this_year {
label: "Count Orders this Year"
type: "number"
// highlight-next-line
definition: @aql orders.count_orders | where(orders.created_at matches @(this year)) ;;
}
measure percent_change_2023_this_year {
label: "Percent Change 2022 and This Year"
type: "number"
definition: @aql
safe_divide(
(orders.count_orders_this_year - orders.count_orders_2023)*1.0,
orders.count_orders_2023
);;
}Example 1
explore {
measures {
orders.count_orders_last_year,
orders.count_orders_2022,
orders.percent_change_2022_this_year
}
}| Count Orders Last Year | Count Orders 2022 | Percent Change |
|---|---|---|
| 408 | 1102 | -0.62976406533575317604 |
Example 2
explore {
dimensions {
countries.continent_name
}
measures {
orders.count_orders_last_year,
orders.count_orders_2022,
orders.percent_change_2022_this_year
}
}| Continent Name | Count Orders Last Year | Count Orders 2022 | Percent Change |
|---|---|---|---|
| America | 43 | 129 | -0.66666666666666666667 |
| Australia | 52 | 119 | -0.56302521008403361345 |
| Asia | 170 | 516 | -0.67054263565891472868 |
| Europe | 143 | 338 | -0.57692307692307692308 |
Method 3: Fixed period and fixed granularity
If you need to compare a specific period to a series of consecutive periods, you can employ the of_all() function to fulfill this use case.
# orders.model.aml
...
measure count_orders {
label: "Count Orders"
type: "number"
definition: @aql count(orders.id) ;;
}
measure count_orders_fixed_holiday_season_2022 {
label: "Count Orders Holiday Season 2022"
type: "number"
definition: @aql orders.count_orders
| where(orders.created_at matches @2022-12)
| of_all(orders)
;;
}
measure percent_difference_fixed_holiday_season_2022 {
label: "Percent Diffence to Fixed Holiday 2022"
type: "number"
definition: @aql safe_divide(
orders.count_orders*1.0,
orders.count_orders_fixed_holiday_season_2022
) ;;
}Example
explore {
dimensions {
orders.created_at | month()
}
measures {
orders.count_orders,
orders.count_orders_fixed_holiday_season_2022,
orders.percent_difference_fixed_holiday_season_2022
}
filters {
orders.created_at matches @(last 2 years)
}
}| Month | Count Orders | Count Orders Holiday 2022 | Percent Difference |
|---|---|---|---|
| 2022-01-01 00:00:00.000000 | 58 | 141 | 0.41134751773049645390 |
| 2022-02-01 00:00:00.000000 | 52 | 141 | 0.36879432624113475177 |
| 2022-03-01 00:00:00.000000 | 63 | 141 | 0.44680851063829787234 |
| 2022-04-01 00:00:00.000000 | 92 | 141 | 0.65248226950354609929 |
| 2022-05-01 00:00:00.000000 | 86 | 141 | 0.60992907801418439716 |
| 2022-06-01 00:00:00.000000 | 92 | 141 | 0.65248226950354609929 |
| 2022-07-01 00:00:00.000000 | 98 | 141 | 0.69503546099290780142 |
| 2022-08-01 00:00:00.000000 | 77 | 141 | 0.54609929078014184397 |
| 2022-09-01 00:00:00.000000 | 103 | 141 | 0.73049645390070921986 |
| 2022-10-01 00:00:00.000000 | 103 | 141 | 0.73049645390070921986 |
| 2022-11-01 00:00:00.000000 | 137 | 141 | 0.97163120567375886525 |
| 2022-12-01 00:00:00.000000 | 141 | 141 | 1.00000000000000000000 |
| 2023-01-01 00:00:00.000000 | 171 | 141 | 1.2127659574468085 |
| 2023-02-01 00:00:00.000000 | 161 | 141 | 1.1418439716312057 |
| 2023-03-01 00:00:00.000000 | 76 | 141 | 0.53900709219858156028 |
Method 4: Fixed period and dynamic granularity
Similar to Method 2, this approach enables explicit filtering on the desired period. However, it also provides the flexibility to separate its context from the filter conditions which is applied at the exploration layer. We’ll employ exact_period() to perform this use case. In this example, we’ll compare a metric at an arbitrary period to Holiday Season period
# orders.model.aml
...
measure count_orders {
label: "Count Orders"
type: "number"
definition: @aql count(orders.id) ;;
}
measure count_orders_holiday_2022 {
label: "Count Orders Holiday Season 2022"
type: "number"
// highlight-next-line
definition: @aql orders.count_orders | exact_period(orders.created_at, @2022-12-01 - 2023-12-31);;
}
measure percent_change_to_holiday_2022 {
label: "Percent Difference to Holiday Season 2022"
type: "number"
definition: @aql safe_divide(orders.count_orders*1.0, orders.count_orders_holiday_season_2022) ;;
}Example 1
explore {
measures {
orders.count_orders,
orders.count_orders_holiday_season_2022,
orders.percent_change_to_holiday_2022
}
filters {
orders.created_at matches @(last month)
}
}| Count Orders | Count Orders Holiday 2022 | Percent Difference |
|---|---|---|
| 76 | 175 | 0.43428571428571428571 |
Example 2
explore {
dimensions {
countries.continent_name
}
measures {
orders.count_orders,
orders.count_orders_holiday_season_2022,
orders.percent_difference_to_holiday_2022
}
filters {
orders.created_at matches @(last month)
}
}| Continent Name | Count Orders | Count Orders Holiday 2022 | Percent Difference |
|---|---|---|---|
| America | 7 | 24 | 0.29166666666666666667 |
| Australia | 12 | 22 | 0.54545454545454545455 |
| Asia | 31 | 71 | 0.43661971830985915493 |
| Europe | 26 | 58 | 0.44827586206896551724 |
Example 3
The exact_period() function ensures that the condition applied to the created_at() field at the exploration level does not affect this measure. However, if created_at() is used as a dimension and the exploration is set to a smaller granularity, the comparison will be between two periods at that specific level of detail.
For example, if created_at() is set to a daily granularity and the condition is applied as last month, the exploration will provide a day-by-day comparison between the previous month and the holiday season of 2022.
explore {
dimensions {
orders.created_at | day()
}
measures {
orders.count_orders,
orders.count_orders_holiday_season_2022,
orders.percent_difference_to_holiday_2022
}
filters {
orders.created_at matches @(last month)
}
}| Date | Count Orders | Count Orders Holiday 2022 | Percent Difference |
|---|---|---|---|
| 2022-10-01 00:00:00.000000 | 4 | 4 | 1.00000000000000000000 |
| 2022-10-02 00:00:00.000000 | 2 | 5 | 0.40000000000000000000 |
| 2022-10-03 00:00:00.000000 | 5 | 8 | 0.62500000000000000000 |
| 2022-10-04 00:00:00.000000 | 4 | 4 | 1.00000000000000000000 |
| 2022-10-05 00:00:00.000000 | 2 | 3 | 0.66666666666666666667 |
| 2022-10-06 00:00:00.000000 | 6 | 5 | 1.2000000000000000 |
| 2022-10-07 00:00:00.000000 | 1 | 2 | 0.50000000000000000000 |
| 2022-10-08 00:00:00.000000 | 2 | 7 | 0.28571428571428571429 |
| 2022-10-09 00:00:00.000000 | null | 4 | null |
| 2022-10-10 00:00:00.000000 | 2 | 4 | 0.50000000000000000000 |
| 2022-10-11 00:00:00.000000 | 4 | 6 | 0.66666666666666666667 |
| 2022-10-12 00:00:00.000000 | 1 | 7 | 0.14285714285714285714 |
| 2022-10-13 00:00:00.000000 | 4 | 7 | 0.57142857142857142857 |
| 2022-10-14 00:00:00.000000 | 12 | 5 | 2.4000000000000000 |
| 2022-10-15 00:00:00.000000 | 5 | 3 | 1.6666666666666667 |
| 2022-10-16 00:00:00.000000 | 2 | 2 | 1.00000000000000000000 |
| 2022-10-17 00:00:00.000000 | 3 | 6 | 0.50000000000000000000 |
| 2022-10-18 00:00:00.000000 | null | 6 | null |
| 2022-10-19 00:00:00.000000 | 4 | 5 | 0.80000000000000000000 |
| 2022-10-20 00:00:00.000000 | 3 | 6 | 0.50000000000000000000 |
| 2022-10-21 00:00:00.000000 | 4 | 2 | 2.0000000000000000 |
| 2022-10-22 00:00:00.000000 | 6 | 3 | 2.0000000000000000 |
| 2022-10-23 00:00:00.000000 | 3 | 5 | 0.60000000000000000000 |
| 2022-10-24 00:00:00.000000 | 4 | 3 | 1.3333333333333333 |
| 2022-10-25 00:00:00.000000 | 2 | null | null |
| 2022-10-26 00:00:00.000000 | 2 | 5 | 0.40000000000000000000 |
| 2022-10-27 00:00:00.000000 | 4 | 2 | 2.0000000000000000 |
| 2022-10-28 00:00:00.000000 | 3 | 4 | 0.75000000000000000000 |
| 2022-10-29 00:00:00.000000 | 5 | 6 | 0.83333333333333333333 |
| 2022-10-30 00:00:00.000000 | 1 | 4 | 0.25000000000000000000 |
| 2022-10-31 00:00:00.000000 | 3 | 8 | 0.37500000000000000000 |
Method 5: Compound annual growth rate (CAGR)
For multi-year growth, a single year-over-year number can be misleading. CAGR smooths growth across N years into one figure, useful for "average annual growth across the period" headlines.
Build it from two relative_period() metrics (current revenue and revenue N years ago). Then apply the CAGR formula:
metric revenue_2_years_ago {
label: 'Revenue 2 Years Ago'
type: 'number'
definition: @aql
revenue
| relative_period(orders.created_at, interval(-2 years))
;;
}
metric revenue_2y_cagr {
label: '2-Year CAGR'
type: 'number'
definition: @aql
(pow(revenue / revenue_2_years_ago, 1.0 / 2) - 1) * 100
;;
}The formula is (end / start)^(1/n) - 1. For an N-year CAGR, replace the 2s with N. Multiply by 100 to express as a percent.
This composes with any dimension. Drop revenue_2y_cagr alongside merchants.name to see each merchant's 2-year growth rate, or categories.name for category-level CAGR.