Time-Series Functions

Spectre supports use of these time series functions. They are usually not used directly, but are instead generated by higher-level specifications in cPlans. However, once generated, they can be used directly if needed. Starting with 7.1, all functions that accept dates and periods as arguments, also accept datetimes.

NOTE: When using a calendar argument, the anchor argument is required. If there is no calendar argument, and if the anchor argument is not provided, then the anchor date is assumed to be today(). If no calendar is specified, "standard" is assumed.

Determine if a date or period is within a last-month-to-date range.
lymtd(calendar : string, date_or_period : date-or-period, anchor : date) : boolean
lymtd(date_or_period : date-or-period, anchor : date) : boolean
Returns true if date_or_period is in the month prior to anchor, and date_or_period is before or equal to the date in that month that corresponds to anchor.
This function is shorthand for `ts_month_to_date(date_or_period, ts_offset("year", -1, date_or_period, anchor))`.

For example:

`lymtd("standard", date("2016/02/01"), date("2017/02/11"))` returns true

`lymtd("offset November", date("2016/02/01"), date("2017/02/11"))` returns true

`lymtd(date("2014/04/04"), date("2014/04/15"))` returns false

`lymtd(date("2013/04/04"), date("2014/04/15"))` returns true

Determine if a date or period is within a last-year-to-date range.
lytd(calendar : string, date_or_period : date-or-period, anchor : date) : boolean
lytd(date_or_period : date-or-period, anchor : date) : boolean
Returns true if date_or_period is in the year prior to anchor, and date_or_period is before or equal to the date in that year that corresponds to anchor.
This function is shorthand for `ts_year_to_date(date_or_period, ts_offset("year", -1, date_or_period, anchor))`.

For example:

`lytd("offset december", date("2015/12/01"), date("2017/02/11"))` returns true

`lytd("offset december", date("2016/03/01"), date("2017/02/11"))` returns false

`lytd("iso 8601", date("2016/02/11"), date("2017/02/11"))` returns true

`lytd(date("2014/03/04"), date("2014/04/01"))` returns false

`lytd(date("2014/03/04"), date("2015/04/01"))` returns true

Determine if a date or period is within a month-to-date range.
mtd(calendar : string, date_or_period : date-or-period, anchor : date) : boolean
mtd(date_or_period : date-or-period, anchor : date) : boolean
Returns true if date_or_period and anchor are in the same month, and date_or_period is before or equal to anchor.
This function is shorthand for `ts_month_to_date(calendar,date_or_period, anchor)`.

For example:

`mtd("standard", date("2017/02/10"), date("2017/02/11"))`returns true

`mtd("offset november", date("2017/03/01"), date("2017/02/11"))` returns false

`mtd(date("2014/04/04"), date("2014/04/15"))` returns true

`mtd(date("2014/04/19"), date("2014/04/15"))` returns false

Determine if a date or period is within a rolling month range.
rolling(calendar : string, month_count : integer, date_or_period : date-or-period, anchor : date) : boolean
rolling(month_count : integer, date_or_period : date-or-period, anchor : date) : boolean
Returns true if the month containing date_or_period is in the span of month_count months that ends with the month containing anchor. This looks at whole months, so even dates that occur after the anchor date are still in the range if they occur in the same month.
This function is shorthand for `ts_rolling_months(month_count, date_or_period, anchor)`.

For example:

`rolling("standard", 2, date("2015/11/30"), date("2017/02/11"))` returns false

`rolling("offset november", 12, date("2016/02/11"), date("2017/01/11"))` returns true

`rolling("offset december", 2, date("2017/02/01"), date("2017/02/11"))` returns true

`rolling(6, date("2014/04/20"), date("2014/04/15"))` returns true

`rolling(6, date("2013/12/01"), date("2014/04/15"))` returns true

`rolling(6, date("2013/10/31"), date("2014/04/15"))` returns false

Determine if a date is the same as the anchor date.
ts_current_day(date : date, anchor : date) : boolean

For example:

`ts_current_day(date("2014/10/24"), date("2014/10/24"))` returns true

`ts_current_day(date("2014/10/23"), date("2014/10/24"))` returns false

Determine if a date or period is in the same half as the anchor date.
ts_current_half(calendar : string, date_or_period : date-or-period, anchor : date) : boolean
ts_current_half(date_or_period : date-or-period, anchor : date) : boolean
The calendar for date_or_period must have a half element in it.

For example:

`ts_current_half(date("2014/07/15"), date("2014/10/24"))` returns true

`ts_current_half(date("2014/11/30"), date("2014/10/24"))` returns true

`ts_current_half(date("2014/06/15"), date("2014/10/24"))` returns false

`ts_current_half(period("iso8601 week", "2014-W42"), date("2014/10/15"))` returns <error>

Determine if a date or period is in the same month as the anchor date.
ts_current_month(calendar : string, date_or_period : date-or-period, anchor : date) : boolean
ts_current_month(date_or_period : date-or-period, anchor : date) : boolean
The calendar for date_or_period must have a month element.

For example:

`ts_current_month("offset december", date("2017/01/10"), date("2017/02/11"))` returns false

`ts_current_month("offset december", date("2017/02/10"), date("2017/02/11"))` returns true

`ts_current_month(date("2014/10/15"), date("2014/10/24"))` returns true

`ts_current_month(date("2014/10/31"), date("2014/10/24"))` returns true

`ts_current_month(date("2014/09/15"), date("2014/10/24"))` returns false

`ts_current_month(period("iso8601 week", "2014-W42"), date("2014/10/15"))` returns <error>

Determine if a date or period is in the same quarter as the anchor date.
ts_current_quarter(calendar: string, date_or_period : date-or-period, anchor : date) : boolean
ts_current_quarter(date_or_period : date-or-period, anchor : date) : boolean
The calendar for date_or_period must have a quarter element.

For example:

`ts_current_quarter(date("2014/10/15"), date("2014/10/24"))` returns true

`ts_current_quarter(date("2014/11/30"), date("2014/10/24"))` returns true

`ts_current_quarter(date("2014/09/15"), date("2014/10/24"))` returns false

`ts_current_quarter(period("iso8601 week", "2014-W42"), date("2014/10/15"))` returns <error>

Determine if a date or period is in the same tertile as the anchor date.
ts_current_tertile(calendar : string, date_or_period : date-or-period, anchor : date) : boolean
ts_current_tertile(date_or_period : date-or-period, anchor : date) : boolean
The calendar for date_or_period must have a tertile element.

For example:

`ts_current_tertile(date("2014/09/15"), date("2014/10/24"))` returns true

`ts_current_tertile(date("2014/11/30"), date("2014/10/24"))` returns true

`ts_current_tertile(date("2014/08/15"), date("2014/10/24"))` returns false

`ts_current_tertile(period("iso8601 week", "2014-W42"), date("2014/10/15"))` returns <error>

Determine if a period is in the same week as the anchor date.
ts_current_week(calendar : string, period : period, anchor : date) : boolean
ts_current_week(period : period, anchor : date) : boolean
The calendar for period must have a week element.

For example:

`ts_current_week(period("iso8601 week", "2014-W42"), date("2014/10/15"))` returns true

`ts_current_week(period("iso8601 week", "2014-W42"), date("2014/10/12"))` returns false

Determine if a date or period is in the same year as the anchor date.
ts_current_year(calendar : string, date_or_period : date-or-period, anchor : date) : boolean
ts_current_year(date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_current_year("offset december", date("2016/02/12"), date("2017/02/11"))` returns false

`ts_current_year("offset december", date("2016/12/11"), date("2017/02/11"))` returns true

`ts_current_year(date("2014/01/15"), date("2014/10/24"))` returns true

`ts_current_year(date("2014/11/30"), date("2014/10/24"))` returns true

`ts_current_year(date("2013/10/24"), date("2014/10/24"))` returns false

`ts_current_year(period("iso8601 week", "2014-W42"), date("2014/03/15"))` returns true

Determine if a date or period is within a specified half in the same year as the anchor date.
ts_half(calendar : string, half_number : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_half(half_number : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_half(1, date("2014/05/15"), date("2014/10/24"))` returns true

`ts_half(1, date("2013/05/15"), date("2014/10/24"))` returns false

`ts_half(1, date("2014/10/24"), date("2014/10/24"))` returns false

`ts_half(2, date("2014/11/24"), date("2014/10/24"))` returns true

Determine if a date or period is within a half-to-date range.
ts_half_to_date(calendar ; string, date_or_period : date-or-period, anchor : date) : boolean
ts_half_to_date(date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_half_to_date(date("2014/06/15"), date("2014/10/24"))` returns false

`ts_half_to_date(date("2014/07/15"), date("2014/10/24"))` returns true

`ts_half_to_date(date("2014/10/24"), date("2014/10/24"))` returns true

`ts_half_to_date(date("2014/10/25"), date("2014/10/24"))` returns false

Determine if a date or period is within a specified month in the same year as the anchor date.
ts_month(calendar : string, month_number : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_month(month_number : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_month("offset december", 2, date("2016/12/10"), date("2017/02/11"))` returns false

`ts_month("offset december", 2, date("2016/01/31"), date("2017/02/11"))` returns true

`ts_month(5, date("2014/05/15"), date("2014/10/24"))` returns true

`ts_month(5, date("2013/05/15"), date("2014/10/24"))` returns false

`ts_month(5, date("2014/10/24"), date("2014/10/24"))` returns false

`ts_month(11, date("2014/11/24"), date("2014/10/24"))` returns true

Determine if a date or period is within a month-to-date range.
ts_month_to_date(calendar : string, date_or_period : date-or-period, anchor : date) : boolean
ts_month_to_date(date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_month_to_date(date("2014/09/15"), date("2014/10/24"))` returns false

`ts_month_to_date(date("2014/10/15"), date("2014/10/24"))` returns true

`ts_month_to_date(date("2014/10/24"), date("2014/10/24"))` returns true

`ts_month_to_date(date("2014/10/25"), date("2014/10/24"))` returns false

Shift a time-series anchor by a specified number of intervals, according to the calendar used by a date or period value.
ts_offset(interval : string, interval_count : integer, date_or_period : date-or-period, anchor : date) : date
This converts anchor into a period of the same type as date_or_period, offsets that period, and returns the first date of the resulting period.

For example:

`ts_offset("year", -1, date("2014/01/01"), date("2012/10/24"))` returns 2011/10/24

`ts_offset("year", -1, period("iso8601 week", "2014-W01"), date("2012/10/24"))` returns 2011/10/24

value("YearMo") < ts_offset("year", -1, value("YearMo"), calc("Anchor")) returns true for any month prior to the "same month last year"

Determine if a date or period is within a specified quarter in the same year as the anchor date.
ts_quarter(calendar : string, quarter_number : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_quarter(quarter_number : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_quarter(2, date("2014/05/15"), date("2014/10/24"))` returns true

`ts_quarter(2, date("2013/05/15"), date("2014/10/24"))` returns false

`ts_quarter(2, date("2014/10/24"), date("2014/10/24"))` returns false

`ts_quarter(4, date("2014/11/24"), date("2014/10/24"))` returns true

Determine if a date or period is within a quarter-to-date range.
ts_quarter_to_date(calendar : string, date_or_period : date-or-period, anchor : date) : boolean
ts_quarter_to_date(date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_quarter_to_date(date("2014/09/15"), date("2014/11/24"))` returns false

`ts_quarter_to_date(date("2014/10/15"), date("2014/11/24"))` returns true

`ts_quarter_to_date(date("2014/11/24"), date("2014/11/24"))` returns true

`ts_quarter_to_date(date("2014/11/25"), date("2014/11/24"))` returns false

Determine if a date is a specified number of days before the anchor date.
ts_recent_day(day_offset : integer, date : date, anchor : date) : boolean

For example:

`ts_recent_day(5, date("2014/10/19"), date("2014/10/24"))` returns true

`ts_recent_day(5, date("2014/10/20"), date("2014/10/24"))` returns false

The following pair generates equivalent results:

column "DAY_NORMAL" `filter(sum(value("Row")), ts_recent_day( 1, value("Date"), date("2000/01/09")))`

column "DAY_FORWARD" `filter(sum(value("Row")), ts_recent_day(-1, value("Date"), date("2000/01/07")))`

Determine if a date or period is a specified number of halves before the anchor date.
ts_recent_half(calendar : string, half_offset : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_recent_half(half_offset : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_recent_half(3, date("2013/06/30"), date("2014/10/24"))` returns true

`ts_recent_half(3, date("2014/07/01"), date("2014/10/24"))` returns false

Determine if a date or period is a specified number of months before the anchor date.
ts_recent_month(calendar : string, month_offset : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_recent_month(month_offset : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_recent_month("offset december", 2, date("2016/12/01"), date("2017/02/11"))` returns true

`ts_recent_month("offset december", 2, date("2017/01/12"), date("2017/02/11"))` returns false

`ts_recent_month(5, date("2014/05/31"), date("2014/10/24"))` returns true

`ts_recent_month(5, date("2014/06/01"), date("2014/10/24"))` returns false

The following pair generates equivalent results:

column "MONTH_NORMAL" `filter(sum(value("Row")), ts_recent_month( 1, value("Date"), date("2000/10/04")))`

column "MONTH_FORWARD" `filter(sum(value("Row")), ts_recent_month(-1, value("Date"), date("2000/08/17")))`

Determine if a date or period is a specified number of quarters before the anchor date.
ts_recent_quarter(calendar : string, quarter_offset : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_recent_quarter(quarter_offset : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_recent_quarter(2, date("2014/06/30"), date("2014/10/24"))` returns true

`ts_recent_quarter(2, date("2014/07/01"), date("2014/10/24"))` returns false

Determine if a date or period is a specified number of tertiles before the anchor date.
ts_recent_tertile(calendar : string, tertile_offset : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_recent_tertile(tertile_offset : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_recent_tertile(2, date("2014/04/30"), date("2014/10/24"))` returns true

`ts_recent_tertile(2, date("2014/05/01"), date("2014/10/24"))` returns false

Determine if a date or period is a specified number of weeks before the anchor date.
ts_recent_week(week_offset : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_recent_week(2, date("2014/05/01"), date("2014/10/24"))` returns false

`ts_recent_week(5, date("2014/09/15"), date("2014/10/24"))` returns true

Determine if a date or period is a specified number of years before the anchor date.
ts_recent_year(calendar : string, year_offset : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_recent_year(year_offset : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_recent_year(3, date("2011/12/31"), date("2014/10/24"))` returns true

`ts_recent_year(3, date("2012/01/01"), date("2014/10/24"))` returns false

Determine if a date is within a rolling-days range.
ts_rolling_days(day_count : integer, date : date, anchor : date) : boolean

For example:

`ts_rolling_days(30, date("2014/09/15"), date("2014/10/24"))` returns false

`ts_rolling_days(30, date("2014/09/25"), date("2014/10/24"))` returns true

`ts_rolling_days(30, date("2014/10/24"), date("2014/10/24"))` returns true

`ts_rolling_days(30, date("2014/10/25"), date("2014/10/24"))` returns false

The following pair generates equivalent results:

column "DAYS_NORMAL" `filter(sum(value("Row")), ts_rolling_days( 5, value("Date"), date("2000/01/15")))`

column "DAYS_FORWARD" `filter(sum(value("Row")), ts_rolling_days(-5, value("Date"), date("2000/01/11")))`

Determine if a date or period is within a rolling-halves range.
ts_rolling_halves(calendar : string, half_count : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_rolling_halves(half_count : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_rolling_halves(3, date("2013/06/30"), date("2014/10/24"))` returns false

`ts_rolling_halves(3, date("2013/07/31"), date("2014/10/24"))` returns true

`ts_rolling_halves(3, date("2014/12/31"), date("2014/10/24"))` returns true

`ts_rolling_halves(3, date("2015/01/01"), date("2014/10/24"))` returns false

Determine if a date or period is within a rolling-months range.
ts_rolling_months(calendar : string, month_count : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_rolling_months(month_count : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_rolling_months(3, date("2014/07/31"), date("2014/10/24"))` returns false

`ts_rolling_months(3, date("2014/08/01"), date("2014/10/24"))` returns true

`ts_rolling_months(3, date("2014/10/31"), date("2014/10/24"))` returns true

`ts_rolling_months(3, date("2014/11/01"), date("2014/10/24"))` returns false

The following pair generates equivalent results:

column "THREE_MONTH_NORMAL" `filter(sum(value("Row")), ts_rolling_months( 3, value("Date"), date("2000/04/10")))`

column "THREE_MONTH_FORWARD" `filter(sum(value("Row")), ts_rolling_months(-3, value("Date"), date("2000/02/17")))`

Determine if a date or period is within a rolling-quarters range.
ts_rolling_quarters(calendar : string, quarter_count : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_rolling_quarters(quarter_count : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_rolling_quarters(2, date("2014/06/30"), date("2014/10/24"))` returns false

`ts_rolling_quarters(2, date("2014/07/01"), date("2014/10/24"))` returns true

`ts_rolling_quarters(2, date("2014/12/31"), date("2014/10/24"))` returns true

`ts_rolling_quarters(2, date("2015/01/01"), date("2014/10/24"))` returns false

Determine if a date or period is within a rolling-tertiles range.
ts_rolling_tertiles(calendar : string, tertile_count : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_rolling_tertiles(tertile_count : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_rolling_tertiles(2, date("2014/04/30"), date("2014/10/24"))` returns false

`ts_rolling_tertiles(2, date("2014/05/01"), date("2014/10/24"))` returns true

`ts_rolling_tertiles(2, date("2014/12/31"), date("2014/10/24"))` returns true

`ts_rolling_tertiles(2, date("2015/01/01"), date("2014/10/24"))` returns false

Determine if a date or period is within a rolling-weeks range.
ts_rolling_weeks(calendar : string, week_count : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_rolling_weeks(week_count : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_rolling_weeks(5, date("2014/09/30"), date("2014/10/24"))` returns true

`ts_rolling_weeks(5, date("2014/07/04"), date("2014/10/24"))` returns false

Determine if a date or period is within a rolling-years range.
ts_rolling_years(calendar : string, year_count : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_rolling_years(year_count : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_rolling_years(3, date("2011/12/31"), date("2014/10/24"))` returns false

`ts_rolling_years(3, date("2012/01/01"), date("2014/10/24"))` returns true

`ts_rolling_years(3, date("2014/12/31"), date("2014/10/24"))` returns true

`ts_rolling_years(3, date("2015/01/01"), date("2014/10/24"))` returns false

Determine if a date or period is within a specified tertile in the same year as the anchor date.
ts_tertile(calendar : string, tertile_number : integer, date_or_period : date-or-period, anchor : date) : boolean
ts_tertile(tertile_number : integer, date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_tertile(2, date("2014/05/15"), date("2014/10/24"))` returns true

`ts_tertile(2, date("2013/05/15"), date("2014/10/24"))` returns false

`ts_tertile(2, date("2014/10/24"), date("2014/10/24"))` returns false

`ts_tertile(3, date("2014/11/24"), date("2014/10/24"))` returns true

Determine if a date or period is within a tertile-to-date range.
ts_tertile_to_date(calendar : string, date_or_period : date-or-period, anchor : date) : boolean
ts_tertile_to_date(date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_tertile_to_date(date("2014/08/15"), date("2014/10/24"))` returns false

`ts_tertile_to_date(date("2014/09/15"), date("2014/10/24"))` returns true

`ts_tertile_to_date(date("2014/10/24"), date("2014/10/24"))` returns true

`ts_tertile_to_date(date("2014/10/25"), date("2014/10/24"))` returns false

Determine if a date or period is within a week-to-date range.
Requires a calendar with both week and day defined.
ts_week_to_date(calendar : string, date_or_period : date-or-period, anchor : date) : boolean
ts_week_to_date(date_or_period : date-or-period, anchor : date) : boolean
Determine if a date or period is within a year-to-date range.
ts_year_to_date(calendar : string, date_or_period : date-or-period, anchor : date) : boolean
ts_year_to_date(date_or_period : date-or-period, anchor : date) : boolean

For example:

`ts_year_to_date(date("2013/12/15"), date("2014/10/24"))` returns false

`ts_year_to_date(date("2014/03/15"), date("2014/10/24"))` returns true

`ts_year_to_date(date("2014/10/24"), date("2014/10/24"))` returns true

`ts_year_to_date(date("2014/10/25"), date("2014/10/24"))` returns false

Determine if a date or period is within a year-to-date range.
ytd(calendar : string, date_or_period : date-or-period, anchor : date) : boolean
ytd(date_or_period : date-or-period, anchor : date) : boolean
Returns true if date_or_period and anchor are in the same year and date_or_period is before or equal to anchor.
This function is shorthand for `ts_year_to_date(date_or_period, anchor)`.

For example:

`ytd("offset december", date("2016/02/28"), date("2017/02/11"))` returns false

`ytd("offset december", date("2016/12/12"), date("2017/02/11"))` returns true

`ytd(date("2014/03/04"), date("2014/04/01"))` returns true

`ytd(date("2014/03/04"), date("2015/04/01"))` returns false

See also: