Skip to main content

Compare Between Periods

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"
    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_divde(
        (orders.count_orders - orders.count_orders_previous_year)*1.0,
        orders.count_orders_previous_year
      );;
  }

Example 1

Count Orders Last Year vs Count Orders 2 Years Ago
explore {
  measures {
    orders.count_orders,
    orders.count_orders_previous_year,
    orders.percent_change_previous_year
  }
  filters {
    orders.created_at matches @(last year)
  }
}
Open in AMQL playground

Example 2

Count Orders Last Year vs Count Orders 2 Years Ago per Continent
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)
  }
}
Open in AMQL playground

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 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"
    definition: @aql orders.count_orders | where(orders.created_at matches @2023) ;;
  } 
  
  measure count_orders_this_year {
    label: "Count Orders this Year"
    type: "number"
    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

Count Orders Last Year vs Count Orders on 2022
explore {
  measures {
    orders.count_orders_last_year,
    orders.count_orders_2022,
    orders.percent_change_2022_this_year
  }
}
Open in AMQL playground

Example 2

Count Orders Last Year vs Count Orders on 2022 per Continent
explore {
  dimensions {
    countries.continent_name
  }
  measures {
    orders.count_orders_last_year,
    orders.count_orders_2022,
    orders.percent_change_2022_this_year
  }
}
Open in AMQL playground

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

Count Orders Last Year vs Count Orders on Holiday Season 2022
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)
  }
}
Open in AMQL playground

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

Count Orders Last Month vs Count Orders on Holiday Season 2022
explore {
  measures {
    orders.count_orders,
    orders.count_orders_holiday_season_2022,
    orders.percent_change_to_holiday_2022
  }
  filters {
    orders.created_at matches @(last month)
  }
}
Open in AMQL playground

Example 2

Count Orders Last Month vs Count Orders on Holiday Season 2022 per Continent
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)
  }
}
Open in AMQL playground

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.

Count Orders Last Month vs Count Orders on Holiday Season 2022 per Day
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)
  }
}
Open in AMQL playground
Let us know what you think about this document :)