relative_period

tip

Holistics has also supported Period Comparison on UI via Dashboard-level Period Comparison and widget-level Period Comparison which use exact_period() and relative_period() behind the scenes.

Definition

Calculates a metric in the active time-range shifted by a specified interval. The active time-range can be the time range specified in a filter (if no time dimension is active) or the time period in each row of a time dimension.

Syntax

relative_period(metric, time_dimension, offset)

Examples

// Using interval for specific time shift
relative_period(orders.total_orders, orders.created_at, interval(-1 month))

// Using number for period-based shift (requires time grain)
relative_period(orders.total_orders, orders.created_at, -1)

// with pipe
orders.total_orders | relative_period(orders.created_at, interval(-1 month))
orders.total_orders | relative_period(orders.created_at, -1)

Input

• metric: A metric that you want to calculate within a relative interval
• time_dimension: A pre-defined datetime/date dimension that is used for shifting
• offset: Either:
  • A relative interval for shifting from the time condition. E.g. interval(-1 month), interval(-7 days)
  • A number that specifies how many periods to shift based on the current time grain. E.g. -1 for previous month when grouped by month, or previous day when grouped by day. Note: When using a number, the date dimension in visualization must have a time grain applied (e.g., | month(), | day()).

Output

The same metric calculated in the active time-range shifted by the specified interval.

Number-based Offset Example

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

explore {
  dimensions {
    rows {
      rollup(order_items.created_at | month())  // Time grain is month
    }
  }
  measures {
    this_month_count: count(order_items.order_id),
    last_month_count: count(order_items.order_id) | relative_period(order_items.created_at, -1)  // Previous month
  }
  filters {
    order_items.created_at matches @2021
  }
}

In this example:

• Since the dimension is grouped by month (| month()), the number -1 means "previous month"
• If the dimension was grouped by quarter (| quarter()), the number -1 would mean "previous quarter"
• Positive numbers shift forward in time, negative numbers shift backward
• If no time grain is specified, an error will be raised

Combine with dimension

Categorical dimension

When combining with categorical dimension with no filtering on the time dimension, relative_period() will have no effect on the metric.

Categorical dimension with filtering on time dimension

When combining with categorical dimension with filtering on the time dimension, relative_period() will shift the time condition by the specified interval in time_interval argument.

Time dimension

Similar to Dashboard previous period comparison, when combining with another time dimension, relative_period(), will shift the time period in each row of the time dimension by the specified interval in time_interval argument.

Sample Usages

We’ll implement a quick Period Comparison analysis on the total_orders metric which is defined as below

Dataset ecommerce {
  (...)

  metric total_orders {
    label: "Total Orders"
    type: "number"
    definition: @aql count(orders.id) ;;
  }
}

And then define total_orders_last_month as this expression:

count(orders.id) | relative_period(orders.created_at, interval(-1 month))

Examples

For all the examples below, the filter on reporting is set as orders.created_at last 1 month

Compare total_orders from with the preceding month

Compare total_orders week-by-week with the previous month

Compare total_orders with the previous month for each continent