trailing_period

Definition

Calculates a metric over a specific number of date periods up to the current period [1]. 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(metric, 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

• metric (required): The metric 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 input metric over a specific number of date periods up to the current period [1].

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. This is especially useful for metrics that represent a ratio or percentage, such as the Percentage of Cancelled Orders, Conversion Rate, etc.

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

Past 3 Months Percentage of Cancelled Orders

metric cancelled_orders_pct = 
  count_if(orders, orders.status == 'cancelled') * 100.0 / count(orders.id);
explore {
  dimensions {
    orders.created_at | month()
  }
  measures {
    cancelled_orders_pct: cancelled_orders_pct,
    _past_3_months_cancelled_rate: 
      cancelled_orders_pct | 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",
    SUM(CASE
    WHEN ("orders"."status" = 'cancelled') THEN 1
    ELSE 0
    END) AS "sum_79e42",
    COUNT("orders"."id") AS "count_orders->id"
  FROM
    "demo"."orders" "orders"
  GROUP BY
    1
), "aql__t4" 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",
    SUM(CASE
    WHEN ("orders"."status" = 'cancelled') THEN 1
    ELSE 0
    END) AS "sum_79e42"
  FROM
    "demo"."orders" "orders"
  GROUP BY
    1
), "aql__t5" AS (
  SELECT
    "aql__t4"."orders->created_at->month" AS "orders->created_at->month",
    SUM("aql__t4_window"."sum_79e42") AS "sum_79e42"
  FROM
    "aql__t4" "aql__t4_window"
    INNER JOIN "aql__t4" ON ("aql__t4_window"."orders->created_at->month" <= "aql__t4"."orders->created_at->month") AND
    ("aql__t4_window"."orders->created_at->month" > ((((CAST ( "aql__t4"."orders->created_at->month" AS timestamptz )) AT TIME ZONE 'Asia/Saigon') + ((-3) * INTERVAL '1 month'))) AT TIME ZONE 'Asia/Saigon')
  GROUP BY
    1
), "aql__t6" 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__t7" AS (
  SELECT
    "aql__t6"."orders->created_at->month" AS "orders->created_at->month",
    SUM("aql__t6_window"."count_orders->id") AS "count_orders->id"
  FROM
    "aql__t6" "aql__t6_window"
    INNER JOIN "aql__t6" ON ("aql__t6_window"."orders->created_at->month" <= "aql__t6"."orders->created_at->month") AND
    ("aql__t6_window"."orders->created_at->month" > ((((CAST ( "aql__t6"."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__t5"."orders->created_at->month", "aql__t7"."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"."sum_79e42") * 100.0) / MAX("aql__t1"."count_orders->id")) AS "cancelled_orders_pct",
  ((MAX("aql__t5"."sum_79e42") * 100.0) / MAX("aql__t7"."count_orders->id")) AS "_past_3_months_cancelled_rate"
FROM
  "aql__t1"
  FULL JOIN "aql__t5" ON "aql__t1"."orders->created_at->month" = "aql__t5"."orders->created_at->month"
  FULL JOIN "aql__t7" ON "aql__t1"."orders->created_at->month" = "aql__t7"."orders->created_at->month"
GROUP BY
  1
HAVING
  (((MAX("aql__t1"."sum_79e42") * 100.0) / MAX("aql__t1"."count_orders->id")) IS NOT NULL) OR
  (((MAX("aql__t5"."sum_79e42") * 100.0) / MAX("aql__t7"."count_orders->id")) IS NOT NULL)

Note that, this is not the same as the average of the percentage of cancelled orders over the last 3 months, but rather the same percentage metric calculated on a different time frame. In this case, when Percentage of Cancelled Orders is defined as:

$$\text{Cancelled \%} = \frac{\text{Count Cancelled Orders}} {\text{Count Orders}}$$

Then the Past 3 Months Percentage of Cancelled Orders is calculated as:

$$\text{Past 3 Months Cancelled \%} = \frac{\text{Count Cancelled Orders in the last 3 months}} {\text{Count Orders in the last 3 months}}$$

Frequently Asked Questions

What happens if the visualization date grain is finer than the period specified in trailing_period?

In that case, trailing period will calculate the metric over this time frame:

$$\text{Current Period \textit{minus} N Period} \lt Date \le \text{Current Period}$$

Examples:

• Trailing 3 Months Metric displayed daily.
• Trailing 1 Year Metric displayed monthly.

What happens if the visualization date grain is coarser than the period specified in trailing_period or if the visualization has no date dimension at all?

In that case, trailing_period will calculate the metric over the last N periods, where N is the number of periods specified in trailing_period.

For example:

• For a Trailing 3 Months Metric displayed yearly, the metric for 2022 will be calculated from October 1st, 2019, to December 31st, 2022 (assuming the last record in 2022 is in December 2022).

  

• For a Trailing 3 Months Metric displayed as a KPI Metric (a single number), the metric will be calculated from October 1st, 2024, to December 31st, 2024 (assuming the last date in the dataset is in December 2022).

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