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 scene

Definition

Takes one measure and calculates it within a relative interval to the active time-period in the current row. Usually, the active time-period is the time condition applied in the reporting layer, or the current period in the active time-series dimension.

tip

Use filtering in reporting to apply your time condition for one measure, then use another measure with relative_period() to compare it with

Syntax

relative_period(measure, time_dimension, interval)

Examples

relative_period(orders.total_orders, orders.created_at, interval(-1 month))

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

Input

• measure: A measure that you want to calculate within a relative interval
• time_dimension: A pre-defined datetime/date dimension that is used for shifting
• time_interval: A relative interval for shifting from the time condition. E.g. interval(-1 month)

Output

The same measure calculated with shifting time

Combine with dimension

Categorical dimension

Similar to other measures, when combining with categorical dimension, relative_period() works by calculating the measure for each dimension and add to the result

Time dimension

Similar to Dashboard custom period comparison, when combining with another time dimension, relative_period() will add the equivalent period specified in time_interval argument alongside to the other measures of that time dimension

Sample Usage s

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

Model orders {
  (...)

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

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

Define total_orders measure with relative period of the preceding month

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

Compare total_orders from with the preceding month

On time-series dimension

Compare total_orders week-by-week with the previous month

On categorical dimension

Compare total_orders with the previous month for each continent