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.
![Closed](../../Skins/Default/Stylesheets/Images/transparent.gif)
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")`
![Closed](../../Skins/Default/Stylesheets/Images/transparent.gif)
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")`
![Closed](../../Skins/Default/Stylesheets/Images/transparent.gif)
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"`
![Closed](../../Skins/Default/Stylesheets/Images/transparent.gif)
Ranges are based on 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.
![Closed](../../Skins/Default/Stylesheets/Images/transparent.gif)
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:
- Time Series Grammar—for a top level view of the block
- Time Series Tags—for a description of the tags used in a time-series block
- Time Series Ranges—for details about these expressions
- Time Series Column Suffixes—for help anticipating column names
- Time Series Examples—for time series examples, including a basic year-to-date and more complicated samples
- Generated Time Series Columns—for a simple example with generated columns
- About Spectre Calendars—for more details on built-in and customer calendars