trailing_period

info

This is part of our beta expression language AQL. Learn more. Request beta.

Definition

Calculates for a specific number of date periods up to the current period. For example, you can apply this computation to determine the total orders you have in the last 3 months up until the current month [1], also known as Trailing 3 Months Metric.

Syntax

trailing_period(measure, date_dimension, period)

Examples

// Return total orders in the last 3 months
trailing_period(count(orders.id), orders.created_at, interval(3 months))

// with pipe
count(orders.id) | trailing_period(orders.created_at, interval(3 months))

Input

• measure (required): The measure on which you want to apply the trailing_period() function
• date_dimension (required): The date dimension that is used to determine the periods
• period (required): An interval literal that specifies the number of periods to calculate (includes the current period). E.g. interval(3 months), interval(1 year).

Output

A metric that calculates the measure for a specific number of date periods up to the current date.

Sample Usages

Trailing periods offer a valuable tool for analyzing how metrics perform over a rolling or moving time frame. Imagine that 3-months is a good time frame to see how a certain metric performs in your business. By using trailing_period you can see how a metric historically performed for the past 3 months at any given point in time.

For example, here's how you can use trailing_period to calculate how the Past 3 Months Total of Orders changes over time:

Trailing 3 Months Total Orders

explore {
  dimensions {
    orders.created_at | month()
  }
  measures {
    count_orders: count(orders.id),
    trailing_3_months: count(orders.id)
    | trailing_period(orders.created_at, interval(3 months))
  }
}

Open in AMQL playground

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"
  GROUP BY
    1
), "aql__t3" 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"
  GROUP BY
    1
), "aql__t4" AS (
  SELECT
    "aql__t3"."orders->created_at->month" AS "orders->created_at->month",
    SUM("aql__t3_window"."count_orders->id") AS "count_orders->id"
  FROM
    "aql__t3" "aql__t3_window"
    INNER JOIN "aql__t3" ON ("aql__t3_window"."orders->created_at->month" <= "aql__t3"."orders->created_at->month") AND
    ("aql__t3_window"."orders->created_at->month" > ((CAST ( "aql__t3"."orders->created_at->month" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' + ((-3) * INTERVAL '1 month')) AT TIME ZONE 'Asia/Saigon')
  GROUP BY
    1
)
SELECT
  TO_CHAR((CAST ( COALESCE("aql__t1"."orders->created_at->month", "aql__t4"."orders->created_at->month") AS timestamptz )) AT TIME ZONE 'Asia/Saigon', 'YYYY-MM-DD HH24:MI:SS.US') AS "orders->created_at->month",
  MAX("aql__t1"."count_orders->id") AS "count_orders",
  MAX("aql__t4"."count_orders->id") AS "trailing_3_months"
FROM
  "aql__t1"
  FULL JOIN "aql__t4" ON "aql__t1"."orders->created_at->month" = "aql__t4"."orders->created_at->month"
GROUP BY
  1
HAVING
  (MAX("aql__t1"."count_orders->id") IS NOT NULL) OR
  (MAX("aql__t4"."count_orders->id") IS NOT NULL)

Trailing periods are also useful for creating moving averages, which can help you smooth out short-term fluctuations and identify long-term trends. For example, you can use trailing_period to calculate the 3-Month Moving Average of Orders:

3-Month Moving Average of Orders

explore {
  dimensions {
    orders.created_at | month()
  }
  measures {
    count_orders: count(orders.id),
    moving_3_months_avg: 
    (count(orders.id) | trailing_period(orders.created_at, interval(3 months))) / 3
  }
}

Open in AMQL playground

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"
  GROUP BY
    1
), "aql__t3" 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"
  GROUP BY
    1
), "aql__t4" AS (
  SELECT
    "aql__t3"."orders->created_at->month" AS "orders->created_at->month",
    SUM("aql__t3_window"."count_orders->id") AS "count_orders->id"
  FROM
    "aql__t3" "aql__t3_window"
    INNER JOIN "aql__t3" ON ("aql__t3_window"."orders->created_at->month" <= "aql__t3"."orders->created_at->month") AND
    ("aql__t3_window"."orders->created_at->month" > ((CAST ( "aql__t3"."orders->created_at->month" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' + ((-3) * INTERVAL '1 month')) AT TIME ZONE 'Asia/Saigon')
  GROUP BY
    1
)
SELECT
  TO_CHAR((CAST ( COALESCE("aql__t1"."orders->created_at->month", "aql__t4"."orders->created_at->month") AS timestamptz )) AT TIME ZONE 'Asia/Saigon', 'YYYY-MM-DD HH24:MI:SS.US') AS "orders->created_at->month",
  MAX("aql__t1"."count_orders->id") AS "count_orders",
  MAX(("aql__t4"."count_orders->id" / 3)) AS "moving_3_months_avg"
FROM
  "aql__t1"
  FULL JOIN "aql__t4" ON "aql__t1"."orders->created_at->month" = "aql__t4"."orders->created_at->month"
GROUP BY
  1
HAVING
  (MAX("aql__t1"."count_orders->id") IS NOT NULL) OR
  (MAX(("aql__t4"."count_orders->id" / 3)) IS NOT NULL)

caution

Only average of additive measures can be calculated this way. For example, you can divide the 3-months trailing total of orders by 3 to get the 3-months moving average of orders. However, you cannot do the same for measures that produce a percentage, or any other non-additive measures. In the future, we will support more complex calculations for non-additive measures.

_{Foot notes:
[1] month in the context of the current row, not the current month in your calendar.}