Time Series Ranges

The specifications for the various Spectre time series ranges are detailed below. See the Generated Time Series Columns for an illustration of these components in practice. Each range consists of two dates, the anchor date and the input date. When an input date falls within the given range of the anchor date, the time series function returns as true. Each time series range has a different start and end, dependent on the type of the range, for which an input date returns true.

  • Predicate expression: ts_current_day
  • Start of range (inclusive): The anchor.
  • End of range (inclusive): The anchor.
  • Name component: "D"
  • Default label component: "D"
  • Predicate expression: ts_current_week
  • Start of range (inclusive): The first day of the week containing the anchor.
  • End of range (inclusive): The last day of the week containing the anchor.
  • Name component: "W"
  • Default label component: "W"
  • Predicate expression: ts_current_month
  • Start of range (inclusive): The first day of the month containing the anchor.
  • End of range (inclusive): The last day of the month containing the anchor.
  • Name component: "M"
  • Default label component: "M"
  • Predicate expression: ts_current_quarter
  • Start of range (inclusive): The first day of the quarter containing the anchor.
  • End of range (inclusive): The last day of the quarter containing the anchor.
  • Name component: "Q"
  • Default label component: "Q"
  • Predicate expression: ts_current_tertile
  • Start of range (inclusive): The first day of the tertile containing the anchor.
  • End of range (inclusive): The last day of the tertile containing the anchor.
  • Name component: "T"
  • Default label component: "T"
  • Predicate expression: ts_current_half
  • Start of range (inclusive): The first day of the half containing the anchor.
  • End of range (inclusive): The last day of the half containing the anchor.
  • Name component: "H"
  • Default label component: "H"
  • Predicate expression: ts_current_year (equivalent to year(<date>) = year(<anchor>))
  • Start of range (inclusive): The first day of the year containing the anchor.
  • End of range (inclusive): The last day of the year containing the anchor.
  • Name component: "Y"
  • Default label component: "Y"
  • Predicate expression: ts_week_to_date
  • Start of range (inclusive): The first day of the week containing the anchor.
  • End of range (inclusive): The anchor.
  • Name component: "WTD"
  • Default label component: "WTD"

month-to-date

  • Predicate expression: mtd
  • Start of range (inclusive): The first day of the month containing the anchor.
  • End of range (inclusive): The anchor.
  • Name component: "MTD"
  • Default label component: "MTD"
  • Predicate expression: ts_quarter_to_date
  • Start of range (inclusive): The first day of the quarter containing the anchor.
  • End of range (inclusive): The anchor.
  • Name component: "QTD"
  • Default label component: "QTD"
  • Predicate expression: ts_tertile_to_date
  • Start of range (inclusive): The first day of the tertile containing the anchor.
  • End of range (inclusive): The anchor.
  • Name component: "TTD"
  • Default label component: "TTD"
  • Predicate expression: ts_half_to_date
  • Start of range (inclusive): The first day of the half containing the anchor.
  • End of range (inclusive): The anchor.
  • Name component: "HTD"
  • Default label component: "HTD"
  • Predicate expression: ytd
  • Start of range (inclusive): The first day of the year containing the anchor.
  • End of range (inclusive): The anchor.
  • Name component: "YTD"
  • Default label component: "YTD"

This range specification produces one calc per summary per provided range in a year. The specification produces more calcs when using variations.

  • This produces 12 calcs per summary.
  • Predicate expression: ts_month
  • Start of range (inclusive): The first day of month <month number> in the year containing the anchor.
  • End of range (inclusive): The last day of month <month number> in the year containing the anchor.
  • Name component: "M##" (such as "M01")
  • Default label component: "MMM" (such as "Jan")
  • This produces 4 calcs per summary.
  • Predicate expression: ts_quarter
  • Start of range (inclusive): The first day of quarter <quarter number> in the year containing the anchor.
  • End of range (inclusive): The last day of quarter <quarter number> in the year containing the anchor .
  • Name component: "Q#" (such as "Q1")
  • Default label component: "Q#" (such as "Q1")
  • This produces 3 calcs per summary.
  • Predicate expression: ts_tertile
  • Start of range (inclusive): The first day of tertile <tertile number> in the year containing the anchor.
  • End of range (inclusive): The last day of tertile <tertile number> in the year containing the anchor.

  • Name component: "T#" (such as "T1")

  • Default label component: "T#" (such as "T1")
  • This produces 2 calcs per summary.
  • Predicate expression: ts_half
  • Start of range (inclusive): The first day of half <half number> in the year containing the anchor.
  • End of range (inclusive): The last day of half <half number> in the year containing the anchor.
  • Name component: "H#" (such as "H1")
  • Default label component: "H#" (such as "H1")

This range specification requires a <count> variable. The <count> variable informs the start of the range.

  • Predicate expression: ts_rolling_days
  • Start of range (inclusive): <count>-1 days before the anchor.
  • End of range (inclusive): The anchor.
  • Name component: "RD#" (such as "RD60")
  • Default label component: "RD#" (such as "RD60")
  • Predicate expression: ts_rolling_weeks
  • Start of range (inclusive): The first date in the week <count>-1 weeks before the week containing the anchor.
  • End of range (inclusive): The last day of the week containing the anchor.
  • Name component: "RW#" (such as "RW8")
  • Default label component: "RW#" (such as "RW8")
  • Predicate expression: ts_rolling_months
  • Start of range (inclusive): The first date in the month <count>-1 months before the month containing the anchor.
  • End of range (inclusive): The last day of the month containing the anchor.
  • Name component: "R#" (such as "R6")
  • Default label component: "R#" (such as "R6")
  • Predicate expression: ts_rolling_quarters
  • Start of range (inclusive): The first date in the quarter <count>-1 quarters before the quarter containing the anchor.
  • End of range (inclusive): The last day of the quarter containing the anchor.
  • Name component: "RQ#" (such as "RQ3")
  • Default label component: "RQ#" (such as "RQ3")
  • Predicate expression: ts_rolling_tertiles
  • Start of range (inclusive): The first date in the tertile <count>-1 tertiles before the tertile containing the anchor.
  • End of range (inclusive): The last date in the tertile containing the anchor.
  • Name component: "RT#" (such as "RT2")
  • Default label component: "RT#" (such as "RT2")
  • Predicate expression: ts_rolling_halves
  • Start of range (inclusive): The first date in the half <count>-1 halves before the half containing the anchor.
  • End of range (inclusive): The last day in the half containing the anchor.
  • Name component: "RH#" (such as "RH3")
  • Default label component: "RH#" (such as "RH3")
  • Predicate expression: ts_rolling_years
  • Start of range (inclusive): The first date in the year <count>-1 years before the year containing the anchor.
  • End of range (inclusive): The last day in the year containing the anchor.
  • Name component: "RY#" (such as "RY3")
  • Default label component: "RY#" (such as "RY3")

This option creates a number of calcs equal to the <count> variable, one for each offset between 0 and <count> - 1.

  • Predicate expression: ts_recent_day
  • Start of range (inclusive): <count> days before the anchor.
  • End of range (inclusive): same as start of range.
  • Name component: "D-#" (such as "D-60")
  • Default label component: "D-#" (such as "D-60")
  • Predicate expression: ts_recent_week
  • Start of range (inclusive): The first day of the week <count> weeks before the week containing the anchor.
  • End of range (inclusive): The last day of the week <count> weeks before the week containing the anchor.
  • Name component: "W-#" (such as "W-8")
  • Default label component: "W-#" (such as "W-8")
  • Predicate expression: ts_recent_month
  • Start of range (inclusive): The first day of the month <count> months before the month containing the anchor.
  • End of range (inclusive): The last day of the month <count> months before the month containing the anchor.
  • Name component: "M-#" (such as "M-6")
  • Default label component: "M-#" (such as "M-6")
  • Predicate expression: ts_recent_quarter
  • Start of range (inclusive): The first day of the quarter <count> quarters before the quarter containing the anchor.
  • End of range (inclusive): The last day of the quarter <count> quarters before the quarter containing the anchor.
  • Name component: "Q-#" (such as "Q-3")
  • Default label component: "Q-#" (such as "Q-3")
  • Predicate expression: ts_recent_tertile
  • Start of range (inclusive): The first day of the tertile <count> tertiles before the tertile containing the anchor.
  • End of range (inclusive): The last day of the tertile <count> tertiles before the tertile containing the anchor.
  • Name component: "T-#" (such as "T-2")
  • Default label component: "T-#" (such as "T-#2")
  • Predicate expression: ts_recent_half
  • Start of range (inclusive): The first day of the half <count> halves before the half containing the anchor.
  • End of range (inclusive): The last day of the half <count> halves before the half containing the anchor.
  • Name component: "H-#" (such as "H-2")
  • Default label component: "H-#" (such as "H-2")
  • Predicate expression: ts_recent_year
  • Start of range (inclusive): The first day of the year <count> years before the year containing the anchor.
  • End of range (inclusive): The last day of the year <count> years before the year containing the anchor.
  • Name component: "Y-#" (such as "Y-3")
  • Default label component: "Y-#" (such as "Y-3")

custom <name> start=<expression> end=<expression>

  • Predicate function: <date> >= <start-date> and <date> <= <end-date>
  • Start of range (inclusive): As specified.
  • End of range (inclusive): As specified.
  • Name component: Must be specified
  • Default label component: Same as the name component

This variation is implied, but it can be specified explicitly to change the label component (such as to "Current Year").

  • Name component: ""
  • Default label component: ""
  • Formula: Compute the range with the anchor pushed back 1 year.
  • Name component: "LY"
  • Default label component: "LY"
  • Formula: (TY - LY)
  • Name component: "TY Diff"
  • Default label component: "TY Diff"
  • Formula: pct_var(TY, LY)
  • Name component: "TY % Diff"
  • Default label component: "TY % Diff"
  • Default format: "#,##0.0\\%" (overrides the summary, range, variation format, but can be overridden in the variation)

This produces <count> additional columns per range per summary.

  • Formula: Compute the range with the anchor pushed back <index> years.
  • Name component: "Y-#" (such as "Y-1")
  • Default label component: "Y-#" (such as "Y-1")
  • Formula: (Y-<index>) - (Y-<index+1>)
  • Name component: "Y-# Diff" (such as "Y-1 Diff")
  • Default label component: "Y-# Diff" (such as "Y-1 Diff")
  • Formula: pct_var((Y-<index+1>), (Y-<index>))
  • Name component: "Y-# % Diff" (such as "Y-1 % Diff")
  • Default label component: "Y-# % Diff" (such as "Y-1 % Diff") Default format: "#,##0.0\\%" (overrides summary, range, variation format, but can be overridden in the subvariation)
  • Formula: Compute the range with the anchor pushed back based on the range. For example, previous-range for year-to-date is pushed back one year, previous-range for month-to-date is pushed back one month, previous-range for rolling 3 is pushed back three months.
  • Name component: "LR"for rolling-X; "LY" for year-to-date; "LW" for current week; and similarly for other ranges
  • Default label component: "LR" for rolling-X; "LY" for year-to-date; "LW" for current week; and similarly for other ranges
  • Formula: (TR - LR)
  • Name component: "TR Diff" for rolling-X; "TY Diff" for year-to-date; "TW Diff" for current-week; and similarly for other ranges
  • Default label component: "TR Diff"for rolling-X; "TY Diff" for year-to-date; "TW Diff" for current-week; and similarly for other ranges
  • Formula: pct_var(TR, LR)
  • Name component: "TR % Diff" for rolling-X; "TY % Diff" for year-to-date; "TW % Diff" for current-week; and similarly for other ranges
  • Default label component: "TR % Diff" for rolling-X; "TY % Diff" for year-to-date; "TW % Diff" for current-week; and similarly for other ranges
  • Default format: "#,##0.0\\%" (overrides summary, range, variation format, but can be overridden in the variation)

This produces <count> additional columns per range per summary for a rolling-X, current-X, or X-to-date time period.

  • Formula: Compute the range with the anchor pushed back based on the range. For example, previous-ranges for year-to-date is pushed back X years, previous-ranges for month-to-date is pushed back X months, previous-ranges for rolling Y is pushed back X*Y months.
  • Name component: "R-#" (such as "R-1") for rolling-X; "Y-#" for year-to-date; "W-#" for week-to-date; and similarly for other ranges.
  • Default label component: "R-#" (such as "R-1") for rolling-X; "Y-#" for year-to-date; "W-#" for week-to-date; and similarly for other ranges
  • Formula: (R-<index>) - (R-<index+1>)
  • Name component: "R-# Diff" (such as "R-0 Diff", "R-1 Diff") for rolling-X; "Y-# Diff" for year-to-date; "W-# Diff" for current-week; and similarly for other ranges
  • Default label component: "R-# Diff" (such as "R-0 Diff", "R-1 Diff") for rolling-X; "Y-# Diff" for year-to-date; "W-# Diff" for current-week; and similarly for other ranges
  • Formula: pct_var((R-<index+1>), (R-<index>))
  • Name component: "R-# % Diff" (such as"R-0 % Diff", "R-1 % Diff")for rolling-X; "Y-# %Diff" for year-to-date; "W-# %Diff" for current-week; and similarly for other ranges
  • Default label component: "R-# % Diff" (such as "R-0 % Diff", "R-1 % Diff") for rolling-X; "Y-# %Diff" for year-to-date; "W-# %Diff" for current-week; and similarly for other ranges
  • Default format: "#,##0.0\\%" (overrides summary, range, variation format, but can be overridden in the subvariation)