Formulas and Time Series

Defining Formulas

To perform calculations based on time series values in HAKOM TSM formulas can be defined. Formulas can be either permanently stored in form of a formula time series or evaluated ad hoc without any persistence. 

Persisted Formulas

Formulas can be persisted using time series definition Formula property. Although evaluation results of formula time series are calculated on the fly (and as such no calculation data is being persisted in common time series data tables), formula time series behave in all conditions as normal time series with persisted data. As such formula time series can be read in different raster, or you may use audit and quotation dates to include underlying (referenced time series) values with their respective audit/quotation version.

As all aggregations in HAKOM TSM also formula time series require an interval for processing the calculation data. The calculation interval is the interval of the formula time series as stored in the time series definition. Of course, the calculation results can be read out at a different interval (e.g. calculate based on hourly values, but output in a daily interval).

Effective Date for Formula Validity

In specific cases parts of a given formula must be modified over time (e.g. changing constants or changing logic after a given effective date). Such changing formulas can be easily modelled in HAKOM TSM by assigning effective dates to formulas, from which they become valid.

Example

One simple formula with two effective dates

Time Series A and B and result of the Formula along the time line:

Creating a Formula Time Series

To assign a formula to a time series, follow these steps:

TSM App:

1. Click on Load in the TSM Ribbon
2. Press the Search... button in the Time series area of the TSM window
3. Select the Edit tab in the Time Series Search window
4. Enter name, type, interval and unit
5. Change from Standard to Formula using the radio button
6. Enter the required formula
7. Press the Save button

WebTSM Services API: 

Formula including effective date for formula validity can be defined in the Formula property of the time series definition:

• POST /repositories/:repository/timeseries/:id|name

• POST /repositories/:repository/timeseriescollections/definition

  Formula:{
  	"1971-12-31T23:00:00Z": "<formula>",
  	"2017-04-01T00:00:00Z": "<formula>"
  }

Note

Evaluation:

Results of formula time series are always determined live and are not persisted. Thus, the results are not only always up-to-date, but also different intervals, units, audit/quotation dates etc. can be taken into account when retrieving evaluated data.

Performance:

This "live evaluation" feature usually does not have a negative effect on performance. Depending on the use case, it can be sometimes advisable to persist aggregation results in a dedicated workflow.

Ad-hoc Formula

Formulas can also be submitted to HAKOM TSM for evaluation without a formula time series.

TSM App:

1. Click on Load in the TSM Ribbon
2. Select Interval, Unit and if necessary further parameters (such as descending and / or missing as null)

3. Select any time series as calculation template* in Time series section via Name selection list (or search functionality)

   

   Tip

   The evaluation engine will use the interval of the specified time series as calculation interval.

4. Enter a (or select a predefined) formula in Time series section Formula selection list
   1. Optional: select a calculation interval using Calc. increment selection list
5. Press the Open button
   

   

TSM Excel Template:

1. Select a time series column, that will serve as a calculation template* for the calculation
2. Enter the required formula in line 14
   
   1. Optional: enter a calculation interval at the end of the formula, separated by semicolon (analogous to the list of intervals in TSM App), e.g. ";1h" for one hour interval
3. Press the Update button
   

   

* Calculation template

To process a formula, TSM needs some information, such as an interval. For this purpose, any time series in the desired interval can be selected.

Tip

The calculation interval can be different from the display interval.

The calculation interval determines the interval at which the calculation takes place. The display interval determines the final display format in which the results are displayed.

• If no calculation interval was specified, the formula is calculated in the interval of the time series and then converted to the display interval.
• If a calculation interval was selected, the calculation is carried out in this interval and then converted to the display interval.

WebTSM Services API:

• Via /repositories/:repository/calculations one or more formulas can be transmitted for calculation. See Validating Formula via WebTSM Services API.

Syntax

In formulas all common operators, functions from the Microsoft Math library and other functions provided by HAKOM can be used. The syntax for formulas is described in detail here: Formula Syntax

Referencing Time Series in Formulas

To reference a time series in a formula, the following pattern must be followed: 

[Time series name OR ID, Optional: Offset, Optional: Unit, Optional: Aggregation]

• Time series name or ID: Mandatory parameter: either the name or the ID of the time series. The name is automatically resolved as ID when saving. It is possible to read out the name of the time series again when the respective time series definition is retrieved. In the TSM App this is possible at the Time Series Edit Window ("Time series name" radio button) and at the Master Data Import and Export ("Output time series names in formula" checkbox) and respectively in the REST API under /repositories/:repository/timeseries (Parameter resolveFormulaReferences).
• Offset: An optional integer value, which fetches additional data from the past (negative number) or from the future (positive number) by the corresponding number of interval increments (so that rolling calculations using values before or after the displayed time range can be realized). To skip this parameter, it must be set to 0
• Unit: The unit in which the data of the referenced time series should be retrieved
• Aggregation: Aggregation rule to be applied to the referenced time series within the formula

Examples

[MyTimeSeries] --> Referenced to "MyTimeSeries
[MyTimeSeries,10] --> Referenced to "MyTimeSeries" loads 10 interval steps from the future in addition to the desired time range
[MyTimeSeries,0,KWh] --> Load "MyTimeSeries" in unit "KWh
[MyTimeSeries,0,KWh,Average] --> Load "MyTimeSeries" in unit "KWh", aggregated with the rule "Average" (instead of "Sum")

Validating a Formula

Formula can either be validated directly at capturing (using the TSM App) or by running the formula.

TSM App:

1. Click on Load in the TSM Ribbon
2. Press the Search... button in the Time series area of the TSM window
3. Select the Edit tab in the Time Series Search window
4. Enter name, type, interval and unit
5. Change from Standard to Formula using the radio button
6. Enter the required formula
7. Press the Check formula button

WebTSM Services API:

• Single time series formulas can be validated using the /calculations path via GET request. A dummy from and to parameter and calculationInterval is sufficient for this purpose:

  GET /repositories/:reposiory/calculations
  ?from=<fromDate>&to=<toDate>&calculationInterval=<calculation-interval>&calculationIntervalMultiplier=<calculation-intervalMultiplier>&formula=<formula>

• Multiple or more complex time series formulas can also be validated using the /calculations path via POST Request. Again, a dummy from and to parameter and calculationInterval must be provided.

  POST/repositories/:reposiory/calculations

  Minimum Request Body:

  JS

  [
      {
          "Name": "<Name of the formula>",
          "Formula": "<formula>",
          "From": "<fromDate>",
          "To": "<toDate>",
          "CalculationInterval": "<interval>",
          "CalculationIntervalMultiplier": "<intervalMultiplier>"
      }
  ]

Formulas and Audit/Quotation Values

When querying time series data, formulas forward any quotation and audit time stamps to the respective referenced time series.

Learn more about formula time series and formula functions here: Formula Syntax

We recommend the following videos under Video Tutorials:

• Formula Time Series
• AdHoc Calculation
• Standard Functions