period_to_date
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
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)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 ordersInput
measure(required): The measure on which you want to apply theperiod_to_date()functiondate_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
explore {
dimensions {
orders.created_at | quarter()
}
measures {
count(orders.id) | period_to_date('year', orders.created_at)
}
}| Quarter | Year-to-date Orders |
|---|---|
| 2020-01-01 00:00:00.000000 | 12 |
| 2020-04-01 00:00:00.000000 | 27 |
| 2020-07-01 00:00:00.000000 | 49 |
| 2020-10-01 00:00:00.000000 | 105 |
| 2021-01-01 00:00:00.000000 | 94 |
| 2021-04-01 00:00:00.000000 | 234 |
| 2021-07-01 00:00:00.000000 | 374 |
| 2021-10-01 00:00:00.000000 | 484 |
| 2022-01-01 00:00:00.000000 | 173 |
| 2022-04-01 00:00:00.000000 | 443 |
| 2022-07-01 00:00:00.000000 | 721 |
| 2022-10-01 00:00:00.000000 | 1102 |
| 2023-01-01 00:00:00.000000 | 408 |
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).