period_to_date

info

This is part of our beta expression language AQL. Learn more. Request beta.

Definition

Calculates values for a specific time range up 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

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