About Spectre Time Series

When you have a cBase with data that spans multiple time periods, you can define a Time Series in Spectre to generate a large variety of time-based calculations. Time series calculations, defined in a cPlan, combine a number of elements, as shown in the following table:

Date The date expression is the value that varies by row and is checked against the range to determine if the row is included in a particular calculation.
Anchor

The anchor expression gives the time range an anchor point. An anchor date is required. Relying on the default anchor date `today()` is deprecated.
Summary The summary list identifies which data columns to include in the time series calculations.
Ranges The list of ranges indicate the time spans desired, for example current-month (M) or month-to-date (MTD). A custom option is available.
Variations The variations direct the creation of secondary columns based on the requested ranges, for example previous-year-current-month (M LY), previous-year-month-to-date (MTD LY).

NOTE: Spectre time series always uses the Standard calendar.

The date tag needs to be followed by an expression that resolves to a date or period. The expressions can be a simple column name in quotes, which is translated internally into the expression value("<column name>"), or a more complicated expression. For example:

date "Order Date"

date "Invoice Fiscal Week"

date value("Order YearMo")

date `date(value("Fiscal Year-Month")+"-01","YYYY-MM-DD")`

The anchor tag sets a reference point. Used without an expression, the anchor defaults to `today()`. Any expression specified needs to resolve to a date. For example:

anchor `date(param("Cur Period")+"-01","YYYY-MM-DD")`

anchor `offset(today(), "day", -1-day(today()))`

anchor `period_start(period("/cbases/fiscalweek.cbase",param("Current Week"),"YYYY-WW"))`

anchor `const(value("Last Date"))`

anchor `top(max(value("Ship Date")))`

anchor `date("2013/06/30")`

anchor `date_from_date_number(42256)`

anchor `date("2015-01-01","YYYY-MM-DD")`

Each summary tag produces a new set of calcs corresponding to a particular named summary expression. If you do not provide a summary expression, calc("<Summary Name>") is automatically generated, which will sum numeric columns. You can also provide a filter attribute, which is used in addition to the time series filter.

For example:

Where Units is a column in the cBase:

summary "Units"

Where a Units calc exists:

calc "A1 Units" `calc("Units")` filter=`value("Product") = "Widget A1"`

A summary can refer to that calc:

summary "A1 Units"

Or a filter attribute can be specified with the column name:

summary "Units" filter=`value("Sales Region")="West"`

Ranges are composed of a combination of a base (for example, day, month, or quarter) and a type (for example, current or to-date). Not all combinations are valid:

Base Current To-Date All-Year Rolling Recent
Day current-day     rolling-days recent-days*
Week current-week week-to-date   rolling-weeks recent-weeks**
Month current-month month-to-date all-months rolling-months recent-months
Quarter current-quarter quarter-to-date all-quarters rolling-quarters recent-quarters
Tertile current-tertile tertile-to-date all-tertiles rolling-tertiles recent-tertiles
Half current-half half-to-date all-halves rolling-halves recent-halves
Year current-year year-to-date   rolling-years recent-years

* Only for dates, not periods

** Only for week-based periods, for example, "iso8601 week"

When the new columns are calculated based on the times series specifications, the new column name is based on the summary name and an abbreviation of the range. For a list, see Time Series Column Suffixes. More broadly, when other options are in use, generated column names follow the pattern:

<series> <summary> <range> <variation>

Generated column labels can be controlled by using the label-format tag.

Variations can be one of:

  • default
  • previous-year
  • previous-years <integer>
  • previous-range
  • previous-ranges <integer>

Sub variations can be one of:

  • difference
  • percent-difference

See also:

Mentioned in: