# Period Comparison

> AQL techniques for comparing metrics across time periods in code, covering relative offsets, fixed date ranges, granularity control, and CAGR calculations.

:::info Alternative
We also support [native GUI-based time period comparison](/docs/reporting/period-comparison.md).
:::

## 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

```aml
# e_commerce.dataset.aml
Dataset e_commerce {
  models: [orders, countries]
  relationships: [
    relationship(orders.country_id > countries.id, true),
  ]
}

# orders.model.aml
...

# countries.model.aml
...

```

<!-- 
Table orders {
  id int [pk, not null] // Primary key
  country_id int
  created_at timestamp // Timestamp for when the order was created
  count_orders measure
}
Table countries {
  id int [pk, not null] // Primary key
  country_name varchar
  continent_name varchar
}
Ref: orders.country_id > countries.id

-->

<iframe className='aspect-ratio-16/9' src='https://dbdiagram.io/e/65a4e765ac844320aeeb5137/66f662993430cb846cce663b'> </iframe>

## 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()](/reference/aql/relative_period) to perform that result 

```aml
# 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*

<SampleCode 
  rawResult={example_1a} 
  title = "Count Orders Last Year vs Count Orders 2 Years Ago"
/>

*Example 2*

<SampleCode 
  rawResult={example_1b} 
  title = "Count Orders Last Year vs Count Orders 2 Years Ago per Continent"
/>

## Method 2:  Fixed period vs fixed period

:::tip
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*](#method-3) and [*method 4*](#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()](/reference/aql/where) along with a time operator to filter the specific period from which you want to derive the measure

```aml
# 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*

<SampleCode 
  rawResult={example_2a} 
  title = "Count Orders Last Year vs Count Orders on 2022"
/>

*Example 2*

<SampleCode 
  rawResult={example_2b} 
  title = "Count Orders Last Year vs Count Orders on 2022 per Continent"
/>

## Method 3: Fixed period and fixed granularity {#method-3}
If you need to compare a specific period to a series of consecutive periods, you can employ the [of_all()](/reference/aql/of_all) function to fulfill this use case.

```aml
	
# 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_ 

<SampleCode 
  rawResult={example_4a} 
  title = "Count Orders Last Year vs Count Orders on Holiday Season 2022"
/>

## Method 4: Fixed period and dynamic granularity {#method-4}

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()](/reference/aql/exact_period) to perform this use case. In this example, we’ll compare a metric at an arbitrary period to Holiday Season period

```aml
	
# 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*

<SampleCode 
  rawResult={example_3a} 
  title = "Count Orders Last Month vs Count Orders on Holiday Season 2022"
/>

*Example 2*

<SampleCode 
  rawResult={example_3b} 
  title = "Count Orders Last Month vs Count Orders on Holiday Season 2022 per Continent"
/>

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

<SampleCode 
  rawResult={example_3c} 
  title = "Count Orders Last Month vs Count Orders on Holiday Season 2022 per Day"
/>

## 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:

```aml
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 `2`s 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.