period_to_date

Definition

Calculates a metric from the beginning of year, quarter, month, etc to the current date. For example, you can apply this computation to determine the total orders you have accumulated in sales from the beginning of the year up until the present date, also known as Year-to-Date(YTD) metric

Syntax

period_to_date(measure, date_part, date_dimension)

Examples

count(orders.id) | period_to_date('year', orders.created_at) // Return year-to-date total orders
count(orders.id) | period_to_date('month', orders.created_at) // Return month-to-date total orders

Input

• measure (required): The measure on which you want to apply the period_to_date() function
• date_part (required): The time period for which the measure should reset. It can be one of the following options. Can be one of the followings: 'year', 'quarter', 'month', 'week', 'day'
• date_dimension (required): The date dimension that is used to determine the reset period

Output

A metric that calculates input measure from the beginning of year, quarter, month, etc to the current date.

Sample Usages

Year-to-date Total Orders per Quarter

explore {
  dimensions {
    orders.created_at | quarter()
  }
  measures {
    count(orders.id) | period_to_date('year', orders.created_at)
  }
}

Open in AMQL playground

Result

SQL

WITH "aql__t6" AS (
  SELECT
    (DATE_TRUNC ( 'quarter', (CAST ( "orders"."created_at" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) AT TIME ZONE 'Asia/Saigon' AS "orders->created_at->quarter",
    "orders"."created_at" AS "orders->created_at",
    (DATE_TRUNC ( 'year', (CAST ( "orders"."created_at" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) AT TIME ZONE 'Asia/Saigon' AS "orders->created_at->year",
    COUNT("orders"."id") AS "count_orders->id"
  FROM
    "demo"."orders" "orders"
  GROUP BY
    1,
    2,
    3
), "aql__t2" AS (
  SELECT
    (DATE_TRUNC ( 'quarter', (CAST ( "orders"."created_at" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) AT TIME ZONE 'Asia/Saigon' AS "orders->created_at->quarter",
    MAX("orders"."created_at") AS "orders->created_at",
    MAX((DATE_TRUNC ( 'year', (CAST ( "orders"."created_at" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) AT TIME ZONE 'Asia/Saigon') AS "orders->created_at->year"
  FROM
    "demo"."orders" "orders"
  GROUP BY
    1
)
SELECT
  TO_CHAR((CAST ( "aql__t2"."orders->created_at->quarter" AS timestamptz )) AT TIME ZONE 'Asia/Saigon', 'YYYY-MM-DD HH24:MI:SS.US') AS "orders->created_at->quarter",
  SUM("aql__t2_window"."count_orders->id") AS "orders->count_orders_ytd"
FROM
  "aql__t6" "aql__t2_window"
  LEFT JOIN "aql__t2" ON ("aql__t2_window"."orders->created_at->year" = "aql__t2"."orders->created_at->year" OR ("aql__t2_window"."orders->created_at->year" IS NULL AND "aql__t2"."orders->created_at->year" IS NULL)) AND
  (
    ("aql__t2"."orders->created_at" IS NULL) OR
    ("aql__t2_window"."orders->created_at" <= "aql__t2"."orders->created_at")
  )
GROUP BY
  1

Frequently Asked Questions

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

In that case, period_to_date will use the last date in the current period as the anchor date, and calculates the metric from the start of month/quarter/year/etc (of the anchor date) to the anchor date.

Examples:

• For a Year-to-Date Metric displayed monthly, the metric for March 2022 will be calculated from January 1st, 2022, to March 31st, 2022.

  

• For a Month-to-Date Metric displayed daily, the metric for Jan 3rd, 2022, will be calculated from January 1st, 2022, to January 3rd, 2022.

  

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

In that case, period_to_date will use the last date in the current period as the anchor date, and calculates the metric in the last month/quarter/year/etc (specified in period_to_date) of the anchor date.

For example:

• For a Month-to-Date Metric displayed yearly, the metric for 2022 will be calculated from December 1st, 2022, to December 31st, 2022 (assuming the last record of 2022 is in December 2022).

  

• For a Year-to-Date Metric displayed as a KPI Metric (a single number), the metric will be calculated from January 1st, 2022, to March 31st, 2022 (assuming the last date in the dataset is in March 2022).