trailing_period

DefinitionCopy Anchor Link

Calculates a metric over a specific number of date periods up to the current period (in the context of the current row, not the current month/period on your calendar). For example, you can apply this computation to determine the total orders you have in the last 3 months up until the current month, also known as Trailing 3 Months Metric.

Syntax

trailing_period(metric, date_dimension, period)

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

// Return total orders in the last 3 periods using number (requires time grain)
trailing_period(count(orders.id), orders.created_at, 3)

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

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): Either:
  • An interval literal that specifies the number of periods to calculate (includes the current period). E.g. interval(3 months), interval(1 year).
  • A number that specifies how many periods to include based on the current time grain. E.g. 3 for 3 months when grouped by month, or 3 days when grouped by day. Note: When using a number, the date dimension must have a time grain applied (e.g., | month(), | day()).

Output

A metric that calculates input metric over a specific number of date periods up to the current period.

Number-based Period ExampleCopy Anchor Link

When using a number instead of an interval, the period is determined by the time grain applied to the date dimension:

explore {
  dimensions {
    order_items.created_at | month()  // Time grain is month
  }
  measures {
    this_month_count: count(order_items.order_id),
    last_3_months_count: count(order_items.order_id) | trailing_period(order_items.created_at, 3)  // Last 3 months
  }
}

In this example:

• Since the dimension is grouped by month (| month()), the number 3 means "last 3 months"
• If the dimension was grouped by day (| day()), the number 3 would mean "last 3 days"
• If no time grain is specified, an error will be raised

Sample UsagesCopy Anchor Link

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:

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))
  }
}

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:

Cancelled % = Count Cancelled Orders / Count Orders

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

Past 3 Months Cancelled % = Count Cancelled Orders in the last 3 months / Count Orders in the last 3 months

Frequently Asked QuestionsCopy Anchor Link

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

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

Current Period - N Periods < Date <= 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?Copy Anchor Link

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

See alsoCopy Anchor Link

• exact_period()
• relative_period()
• running_total()
• Time Comparisons