Compare Between Periods
We also support native GUI-based time period comparison.
A grasp of these concepts will help you understand this documentation better:
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
explore {
measures {
orders.count_orders,
orders.count_orders_previous_year,
orders.percent_change_previous_year
}
filters {
orders.created_at matches @(last year)
}
}
- Result
- SQL
WITH "aql__t2" AS (
SELECT
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2023-01-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2024-01-01T00:00:00.000+07:00' AS timestamptz ))
), "aql__t3" AS (
SELECT
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= (((CAST ( CAST ( '2023-01-01T00:00:00.000+07:00' AS timestamptz ) AS timestamptz )) AT TIME ZONE 'Asia/Saigon' + ((-1) * INTERVAL '1 year'))) AT TIME ZONE 'Asia/Saigon') AND
(CAST ( "orders"."created_at" AS timestamptz ) < (((CAST ( CAST ( '2024-01-01T00:00:00.000+07:00' AS timestamptz ) AS timestamptz )) AT TIME ZONE 'Asia/Saigon' + ((-1) * INTERVAL '1 year'))) AT TIME ZONE 'Asia/Saigon')
)
SELECT
"aql__t2"."count_orders->id" AS "orders->count_orders",
"aql__t3"."count_orders->id" AS "orders->count_orders_previous_year",
((("aql__t2"."count_orders->id" - "aql__t3"."count_orders->id") * 1.0) / "aql__t3"."count_orders->id") AS "orders->percent_change_previous_year"
FROM
"aql__t2"
CROSS JOIN "aql__t3"
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)
}
}
- Result
- SQL
WITH "aql__t1" AS (
SELECT
"countries"."continent_name" AS "countries->continent_name",
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
LEFT JOIN "demo"."cities" "cities" ON "users"."city_id" = "cities"."id"
LEFT JOIN "demo"."countries" "countries" ON "cities"."country_code" = "countries"."code"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2023-01-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2024-01-01T00:00:00.000+07:00' AS timestamptz ))
GROUP BY
1
), "aql__t3" AS (
SELECT
"countries"."continent_name" AS "countries->continent_name",
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
LEFT JOIN "demo"."cities" "cities" ON "users"."city_id" = "cities"."id"
LEFT JOIN "demo"."countries" "countries" ON "cities"."country_code" = "countries"."code"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= (((CAST ( CAST ( '2023-01-01T00:00:00.000+07:00' AS timestamptz ) AS timestamptz )) AT TIME ZONE 'Asia/Saigon' + ((-1) * INTERVAL '1 year'))) AT TIME ZONE 'Asia/Saigon') AND
(CAST ( "orders"."created_at" AS timestamptz ) < (((CAST ( CAST ( '2024-01-01T00:00:00.000+07:00' AS timestamptz ) AS timestamptz )) AT TIME ZONE 'Asia/Saigon' + ((-1) * INTERVAL '1 year'))) AT TIME ZONE 'Asia/Saigon')
GROUP BY
1
)
SELECT
COALESCE("aql__t1"."countries->continent_name", "aql__t3"."countries->continent_name") AS "countries->continent_name",
MAX("aql__t1"."count_orders->id") AS "orders->count_orders",
MAX("aql__t3"."count_orders->id") AS "orders->count_orders_previous_year",
MAX(((("aql__t1"."count_orders->id" - "aql__t3"."count_orders->id") * 1.0) / "aql__t3"."count_orders->id")) AS "orders->percent_change_previous_year"
FROM
"aql__t1"
FULL JOIN "aql__t3" ON "aql__t1"."countries->continent_name" = "aql__t3"."countries->continent_name"
GROUP BY
1
HAVING
(MAX("aql__t1"."count_orders->id") IS NOT NULL) OR
(MAX("aql__t3"."count_orders->id") IS NOT NULL) OR
(MAX(((("aql__t1"."count_orders->id" - "aql__t3"."count_orders->id") * 1.0) / "aql__t3"."count_orders->id")) IS NOT NULL)
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"
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
explore {
measures {
orders.count_orders_last_year,
orders.count_orders_2022,
orders.percent_change_2022_this_year
}
}
- Result
- SQL
WITH "aql__t2" AS (
SELECT
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2023-01-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2024-01-01T00:00:00.000+07:00' AS timestamptz ))
), "aql__t3" AS (
SELECT
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2022-01-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2023-01-01T00:00:00.000+07:00' AS timestamptz ))
)
SELECT
"aql__t2"."count_orders->id" AS "orders->count_orders_last_year",
"aql__t3"."count_orders->id" AS "orders->count_orders_2022",
((("aql__t2"."count_orders->id" - "aql__t3"."count_orders->id") * 1.0) / "aql__t3"."count_orders->id") AS "orders->percent_change_2022_this_year"
FROM
"aql__t2"
CROSS JOIN "aql__t3"
Example 2
explore {
dimensions {
countries.continent_name
}
measures {
orders.count_orders_last_year,
orders.count_orders_2022,
orders.percent_change_2022_this_year
}
}
- Result
- SQL
WITH "aql__t1" AS (
SELECT
"countries"."continent_name" AS "countries->continent_name",
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
LEFT JOIN "demo"."cities" "cities" ON "users"."city_id" = "cities"."id"
LEFT JOIN "demo"."countries" "countries" ON "cities"."country_code" = "countries"."code"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2023-01-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2024-01-01T00:00:00.000+07:00' AS timestamptz ))
GROUP BY
1
), "aql__t3" AS (
SELECT
"countries"."continent_name" AS "countries->continent_name",
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
LEFT JOIN "demo"."cities" "cities" ON "users"."city_id" = "cities"."id"
LEFT JOIN "demo"."countries" "countries" ON "cities"."country_code" = "countries"."code"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2022-01-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2023-01-01T00:00:00.000+07:00' AS timestamptz ))
GROUP BY
1
)
SELECT
COALESCE("aql__t1"."countries->continent_name", "aql__t3"."countries->continent_name") AS "countries->continent_name",
MAX("aql__t1"."count_orders->id") AS "orders->count_orders_last_year",
MAX("aql__t3"."count_orders->id") AS "orders->count_orders_2022",
MAX(((("aql__t1"."count_orders->id" - "aql__t3"."count_orders->id") * 1.0) / "aql__t3"."count_orders->id")) AS "orders->percent_change_2022_this_year"
FROM
"aql__t1"
FULL JOIN "aql__t3" ON "aql__t1"."countries->continent_name" = "aql__t3"."countries->continent_name"
GROUP BY
1
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)
}
}
- Result
- SQL
WITH "aql__t1" AS (
SELECT
(DATE_TRUNC ( 'month', (CAST ( "orders"."created_at" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) AT TIME ZONE 'Asia/Saigon' AS "orders->created_at->month",
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2022-01-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2024-01-01T00:00:00.000+07:00' AS timestamptz ))
GROUP BY
1
), "aql__t3" AS (
SELECT
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2022-12-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2023-01-01T00:00:00.000+07:00' AS timestamptz ))
)
SELECT
TO_CHAR((CAST ( "aql__t1"."orders->created_at->month" AS timestamptz )) AT TIME ZONE 'Asia/Saigon', 'YYYY-MM-DD HH24:MI:SS.US') AS "orders->created_at->month",
"aql__t1"."count_orders->id" AS "orders->count_orders",
"aql__t3"."count_orders->id" AS "orders->count_orders_fixed_holiday_season_2022",
("aql__t1"."count_orders->id" * 1.0) / NULLIF("aql__t3"."count_orders->id", 0) AS "orders->percent_difference_fixed_holiday_season_2022"
FROM
"aql__t1"
CROSS JOIN "aql__t3"
WHERE
("aql__t1"."count_orders->id" IS NOT NULL) OR
("aql__t3"."count_orders->id" IS NOT NULL) OR
(("aql__t1"."count_orders->id" * 1.0) / NULLIF("aql__t3"."count_orders->id", 0) IS NOT NULL)
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
explore {
measures {
orders.count_orders,
orders.count_orders_holiday_season_2022,
orders.percent_change_to_holiday_2022
}
filters {
orders.created_at matches @(last month)
}
}
- Result
- SQL
WITH "aql__t2" AS (
SELECT
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2023-03-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2024-02-01T00:00:00.000+07:00' AS timestamptz ))
), "aql__t3" AS (
SELECT
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2022-07-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2022-09-02T00:00:00.000+07:00' AS timestamptz ))
)
SELECT
"aql__t2"."count_orders->id" AS "orders->count_orders",
"aql__t3"."count_orders->id" AS "orders->count_orders_holiday_season_2022",
(("aql__t2"."count_orders->id" * 1.0) / "aql__t3"."count_orders->id") AS "orders->percent_difference_to_holiday_2022"
FROM
"aql__t2"
CROSS JOIN "aql__t3"
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)
}
}
- Result
- SQL
WITH "aql__t1" AS (
SELECT
"countries"."continent_name" AS "countries->continent_name",
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
LEFT JOIN "demo"."cities" "cities" ON "users"."city_id" = "cities"."id"
LEFT JOIN "demo"."countries" "countries" ON "cities"."country_code" = "countries"."code"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2023-03-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2024-02-01T00:00:00.000+07:00' AS timestamptz ))
GROUP BY
1
), "aql__t3" AS (
SELECT
"countries"."continent_name" AS "countries->continent_name",
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
LEFT JOIN "demo"."cities" "cities" ON "users"."city_id" = "cities"."id"
LEFT JOIN "demo"."countries" "countries" ON "cities"."country_code" = "countries"."code"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2022-07-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2022-09-02T00:00:00.000+07:00' AS timestamptz ))
GROUP BY
1
)
SELECT
COALESCE("aql__t1"."countries->continent_name", "aql__t3"."countries->continent_name") AS "countries->continent_name",
MAX("aql__t1"."count_orders->id") AS "orders->count_orders",
MAX("aql__t3"."count_orders->id") AS "orders->count_orders_holiday_season_2022",
MAX((("aql__t1"."count_orders->id" * 1.0) / "aql__t3"."count_orders->id")) AS "orders->percent_difference_to_holiday_2022"
FROM
"aql__t1"
FULL JOIN "aql__t3" ON "aql__t1"."countries->continent_name" = "aql__t3"."countries->continent_name"
GROUP BY
1
HAVING
(MAX("aql__t1"."count_orders->id") IS NOT NULL) OR
(MAX("aql__t3"."count_orders->id") IS NOT NULL) OR
(MAX((("aql__t1"."count_orders->id" * 1.0) / "aql__t3"."count_orders->id")) IS NOT NULL)
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)
}
}
- Result
- SQL
WITH "aql__t1" AS (
SELECT
(DATE_TRUNC ( 'day', (CAST ( "orders"."created_at" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) AT TIME ZONE 'Asia/Saigon' AS "orders->created_at->day",
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2022-10-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2022-11-01T00:00:00.000+07:00' AS timestamptz ))
GROUP BY
1
), "aql__t4" AS (
SELECT
MIN((DATE_TRUNC ( 'day', (CAST ( "orders"."created_at" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) AT TIME ZONE 'Asia/Saigon') AS "orders->created_at"
FROM
"demo"."orders" "orders"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2022-10-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2022-11-01T00:00:00.000+07:00' AS timestamptz ))
), "aql__t3" AS (
SELECT
((CAST ( (DATE_TRUNC ( 'day', (CAST ( "aql__t4"."orders->created_at" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) AT TIME ZONE 'Asia/Saigon' AS timestamptz )) AT TIME ZONE 'Asia/Saigon' + ((EXTRACT(day from ((DATE_TRUNC ( 'day', (CAST ( (DATE_TRUNC ( 'day', (CAST ( "orders"."created_at" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) AT TIME ZONE 'Asia/Saigon' AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) - (DATE_TRUNC ( 'day', CAST ( '2022-12-01' AS date ) ))))) * INTERVAL '1 day')) AT TIME ZONE 'Asia/Saigon' AS "orders->created_at->day",
COUNT("orders"."id") AS "count_orders->id"
FROM
"demo"."orders" "orders"
CROSS JOIN "aql__t4"
WHERE
(CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2022-12-01T00:00:00.000+07:00' AS timestamptz )) AND
(CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2023-01-01T00:00:00.000+07:00' AS timestamptz ))
GROUP BY
1
)
SELECT
TO_CHAR((CAST ( COALESCE("aql__t1"."orders->created_at->day", "aql__t3"."orders->created_at->day") AS timestamptz )) AT TIME ZONE 'Asia/Saigon', 'YYYY-MM-DD HH24:MI:SS.US') AS "orders->created_at->day",
MAX("aql__t1"."count_orders->id") AS "orders->count_orders",
MAX("aql__t3"."count_orders->id") AS "orders->count_orders_holiday_season_2022",
MAX((("aql__t1"."count_orders->id" * 1.0) / "aql__t3"."count_orders->id")) AS "orders->percent_difference_to_holiday_2022"
FROM
"aql__t1"
FULL JOIN "aql__t3" ON "aql__t1"."orders->created_at->day" = "aql__t3"."orders->created_at->day"
GROUP BY
1
HAVING
(MAX("aql__t1"."count_orders->id") IS NOT NULL) OR
(MAX("aql__t3"."count_orders->id") IS NOT NULL) OR
(MAX((("aql__t1"."count_orders->id" * 1.0) / "aql__t3"."count_orders->id")) IS NOT NULL)