X-axis

In this page we dive into the x_axis metric property. We will explain what the x_axis means in the context of a metric, what operations are possible and how to use them to define how metric periods are calculated.

What is the metric x_axis?

If metrics are "measures in context", the X-axis represents that context. The most popular metric context is time. By displaying metric results over time, metric charts quickly show trends and changes in performance.

When creating a metric, the x_axis property is used to define the relevant dataset for calculating the metric in each period. It determines how data is displayed over time and when each relevant entity affects the metric calculation.

Essentially, this decides where entities are "anchored" to the time series X-axis and in which period they should appear in the metric data.

For example, if we would like to measure the number of won opportunities that closed in each period, based on their close_date we can create the following metric schema:

- name: won_opportunities
  entity: opportunity
  operation: count
  filters:
    - sql: $is_won = true
  x_axis:
    period:
      - sql: $macros.PERIOD_IN($close_date)

Configuring the metric x_axis

Configuring the metric x_axis property determines what data will be calculated and presented within each period.

Unlike other ways of grouping data by an object's dimensions, you can use more advanced logic in the x_axis property to group data into periods.

Boost your metric building by using macros to quickly implement the most popular x_axis configuration, or create your own custom logic with standard SQL.

Using macros

Almost all useful metrics end up using the same form of x_axis. To quickly implement these and achieve the desired metric time-series behavior, you can use a pre-built macro.

These macros are pre-built functions that provide a shortcut to the most popular period grouping methods.

The fastest and simplest way to configure the x_axis metric property is with one of the macros detailed below:

Macro	Description	Logic
PERIOD_IN	A single date dimension of the entity is within the period.	period.start <= date <= period_end
PERIOD_OVERLAPS	There is an overlap between the period and a timeframe between two date dimensions of the entity.	(startDate <= period.end) AND (endDate is NULL OR endDate >= period.start)

PERIOD_IN example

The opportunities_created metric uses the PERIOD_IN macro to calculate the number of opportunities whose created_date is within each period:

- name: opportunities_created
  entity: opportunity
  operation: count
  x_axis:
    period:
      - sql: $macros.PERIOD_IN($created_date)

PERIOD_OVERLAPS example

The opportunities_active_in_period metric uses the PERIOD_OVERLAPS macro to calculate the number of opportunities that were active at any time during the period, whether they were:

1. Created in the period
2. Ended in the period
3. Created before the period and closed after

- name: opportunities_active_in_period
  entity: opportunity
  operation: count
  x_axis:
    period:
      - sql: $macros.PERIOD_OVERLAPS($created_date, $close_date)

Using SQL operations

You can flexibly configure the x_axis with standard SQL operations, slicing metric data into periods based on your custom logic. This is similar to configuring the filters property.

The table below details the SQL operations that can be used to configure the x_axis:

SQL	Description	Example
>= Greater than / equals	A single date dimension of the entity is greater than the period (start or end).	- sql: $created_date >= $period.start
<= Less than / equals	A single date dimension of the entity is less than the period (start or end).	- sql: $created_date >= $period.end
BETWEEN period start and end	A single date dimension is between the period start and end.	- sql: $created_date BETWEEN $period.start AND $period.end
BETWEEN date dimensions	The period start or end date is between two date dimensions.	- sql: $period.end BETWEEN $created_date AND $close_date

X-axis of metric types

The configuration of the x_axis property often determines metrics' Functional type (see Metric types)

• For Flow metrics, use the Cohort or To-date configurations.
• For Stock metrics, use Snapshot or Active-in-period configurations.

The specific configuration for each metric depends on the specific business question you would like to answer and the metric value that would provide the most meaningful answer in each time period.

Cohort

Cohort metrics help compare groups based on the time of a specific event, usually the date they were created, closed or passed a signficant milestone. The cohort x_axis configuration groups data into periods based on a specific datetime dimension that occured within that period.

For example, this metric assigns each opportunity to a cohort based on when it was created, grouping them into periods based on their created_date:

- name: opportunities_created
  entity: opportunity
  operation: count
  x_axis:
    period:
      - sql: $macros.PERIOD_IN($created_date)

To-date

To-date metrics aim to provide a cumulative result that shows how a measure has changed thus far. The "to-date" x_axis configuration uses a specific date dimension and compares it to the start of a given time-frame and the end of the selected period (or the current date for the most recent period). The most popular "to-date" metrics are Quarter-to-Date and Year-to-Date.

In this example, the cumulative number of opportunities created during the quarter is calculated.

- name: open_opportunities_snapshot
  entity: opportunity
  operation: count
  x_axis:
    period:
      - sql: $created_date >= $period.as_quarter.start ## Created after the quarter started
      - sql: $created_date > $period.end ## Created before the period ended

This metric will show the number of opportunities created thus far over the weeks or months of the quarter.

Snapshot

Snapshot metrics show the state of data in a single point-in-time. The x_axis configuration of snapshot metrics assigns an entity to a specific period if it meets certain conditions at the start or end of the period.

For example, this metric presents a snapshot of the number of opportunities in the POV stage based on their opportunity_history

- name: pov_snapshot
  entity: opportunity_history
  operation: count
  x_axis:
    period:
      - sql: $created_date <= $period.end
      - sql: $valid_through_date > $period.end OR $valid_through_date is null

Active-in-period

These metrics track any entity that was active at any time during the period. An entity is considered active based on a timeframe defined by two dates, such as start and end dates, stage entry and exit dates, etc. If the timeframe of the entity intersects in any way with the selected time period, it is counted as active for that period. This approach ensures a comprehensive inclusion of all relevant data, for this reason these metrics are usually used as the denominator in formula metrics. Specifically, they are helpful in calculating the rate of conversion or other proportions within the subset of entities active in the period.

To configure this type of metric x_axis you can use the PERIOD_OVERLAP, macro:

- name: opportunities_active_in_period
  entity: opportunity
  operation: count
  x_axis:
    period:
      - sql: $macros.PERIOD_OVERLAPS($created_date, $close_date)

Alternatively, you can manually configure this x_axis type with SQL conditions as follows:

- name: opportunities_active_in_period
  entity: opportunity
  operation: count
  x_axis:
    period:
      - sql: $created_date <= $period.end ## Created no later than the end of the period
      - sql: $close_date >= $period.start ## Closed no earlier than the end of the period

Wrapping up

The x_axis property in Sightfull metrics is essential for accurately organizing and interpreting data over time. This guide outlines its importance and the various ways to configure it, from simple macros to custom SQL operations. Aligning the x_axis with your specific analytical goals is crucial for deriving meaningful insights. For further exploration on metric creation, refer to our comprehensive guides on Metric types and Building your first metric.