Formulas Overview
A formula time series is a specialized type of time series for which each data point is determined by an underlying function. The time series definition is stored in a database containing the formula specified for it. As changes to the time series definition can be archived, previous versions of a formula can be accessed by selecting a historical date when loading that time series. This allows for recalculations or analyses using a formula as it existed at that point in time.
In addition, when reading data from this time series, reading options can be specified which define how a time series - which is referenced in the formula - is to be read. For example it is possible to specify a historic date in order to read older versions of the referenced time series.
When a formula time series data is queried, it is computed based on the relevant formula (plain text) and the resulting values are provided accordingly.
Calculation rules based on other time series can be implemented using formulas. Time series or numbers can be calculated using basic arithmetical operations and standard mathematical functions. Moreover, custom specified functions can be used.
Syntax
Section titled “Syntax”Formulas are written using C# expressions with some additional syntax for time series references and constant data. The expressions allow for basic mathematical operations, referencing time series, and even calling custom functions. The syntax follows standard C# conventions. The system does not support expression trees or imperative programming constructs such as if, while, or variable declarations. Instead, it only supports direct, declarative statements.
Formulas can include operators, functions, and time series references. For more information refer to the specific sections.
Referencing Time Series
Section titled “Referencing Time Series”Time series can be included in a formula according to the following pattern:
[time series (name or ID), unit (optional), interval transformation (optional)]Optional parameters can be left empty.
| Parameter | Description |
|---|---|
| Time Series | Name or ID of the time series to be referenced. The ID needs the prefix id: |
| Unit | Unit into which the data of the referenced time series should be converted. |
| Interval Transformation | Method to be used when transforming the interval of the referenced time series data (applicable if calculation interval is different from the interval of the time series). Use the name of the transformation method, e.g. Average. See Interval Transformation for details. |
Examples
Section titled “Examples”Basic reference by name
Section titled “ Basic reference by name”[SolarData]
Using the time series name as a reference.
Reference by ID
Section titled “ Reference by ID”[id:23]
Using the time series ID as a reference.
Reference with unit and interval transformation
Section titled “ Reference with unit and interval transformation”[SolarData, kWh, Average]
Using the time series name as a reference as well as the specified unit and interval transformation.
Reference by ID with partial parameters
Section titled “ Reference by ID with partial parameters”[id:23, , Sum]
Using the ID of a time series as reference and interval transformation method Sum, but skipping the unit parameter.
Accessing Properties
Section titled “Accessing Properties”The referenced time series passes a data element containing multiple properties. In addition to applying operations on the data directly, specific properties can be accessed:
| Property | Description | Example |
|---|---|---|
.Value |
Retrieves only the value as a double precision floating point number | [id:23].Value |
.Flag |
Retrieves only the flag as an object of the type HAKOM.TSM.Net.Api.Flags.Flag. To get the identifier of the flag use .Flag.Value |
[SolarData].Flag |
.Time |
Retrieves the timestamp as an object of the type System.DateTimeOffset. Refer to the .NET documentation for more information about existing properties. |
[SolarData].Time |
The .Time property also enables using datetime operations such as retrieving the day: [SolarData].Time.Day
Conditional flag replacement
Section titled “ Conditional flag replacement”[id:15].Flag.Value == 19 ? 0 : [id:15].Value
Returns 0 if the flag is Missing (19), otherwise returns the original value.
Day of the month
Section titled “ Day of the month”[id:42].Time.Day
Returns the day of the month on which the data value is placed, as described in the DateTimeOffset.Day documentation.
Different Repository
Section titled “Different Repository”A time series can also be referenced if it is stored in another repository.
Reference by name from different repository
Section titled “ Reference by name from different repository”[RepositoryName.TimeSeriesName]
References a time series from another repository.
Reference by ID from different repository
Section titled “ Reference by ID from different repository”[RepositoryName.id:123]
References a time series by ID from another repository.
Escaping
Section titled “Escaping”Special characters can be escaped using quotes. This allows time series or repositories with dots, commas, or other special characters in the name to be accessed.
Escaping special characters in names
Section titled “ Escaping special characters in names”["Repository.Name"."Time,,Series.Name"]
References a time series with special characters in both repository and time series names.
Escaping special characters in units
Section titled “ Escaping special characters in units”[id:42, "per, kW"]
Uses a unit with a comma in its name.
Escaping quotes
Section titled “ Escaping quotes”["With \" Quote"]
A quote inside of quotes needs to be escaped using backslash \.
Escaping backslashes
Section titled “ Escaping backslashes”["some \"name\" with, \commas and backslashes \\"]
A backslash needs only to be escaped when a quote is right after it. Otherwise it will be escaped automatically.
Constant Data Points
Section titled “Constant Data Points”As part of a formula it is also possible to create a constant data point that can be used for arithmetic operations.
They can be used according to the following pattern:
{Value|Flag}So in order to for example add a 3, Accounted to each value of a time series it would look like:
[SolarData] + {3|20}Examples
Section titled “Examples”Constant value with flag
Section titled “ Constant value with flag”{3|9}
This will return a time series of constant value 3 with flag 9 (Valid).
Overriding flag of time series data
Section titled “ Overriding flag of time series data”{[SolarData].Value|9}
This will return the data of time series SolarData but with the flag 9 (Valid).
Math Operations
Section titled “Math Operations”Formulas support System.Math operations using C# syntax. However, System.Math functions cannot be applied directly to the entire time series. They must be used on specific properties of a time series element, such as the Value, Flag, or Date.
Examples
Section titled “Examples”Square root of values
Section titled “ Square root of values”Math.Sqrt([TemperatureSeries, °C].Value)
This calculates the square root of each temperature data point.
Maximum between two time series
Section titled “ Maximum between two time series”Math.Max([TemperatureSeries, °C].Value, [WaterTemperatureSeries, °C].Value)
This calculates the maximum value for each of the data points on the two given time series.
Operators
Section titled “Operators”In addition to the basic arithmetic operations that can be applied directly to time series references, such as addition (+), subtraction (-), multiplication (*), and division (/), all other standard C# operators are supported when operating on the values of time series. This includes mathematical, logical, and comparison operators.
Supported for time series references and constant data points:
| Operator | Symbol |
|---|---|
| Addition | + |
| Subtraction | - |
| Multiplication | * |
| Division | / |
| Modulo | % |
Supported when operating on the values or flags of time series:
| Operation | Operators |
|---|---|
| Arithmetic | %, &, ^, <<, >>, … |
| Equality/Inequality | ==, != |
| Comparison | >, >=, <, <= |
| Type Testing | is, as |
| Conditional | a ? b : c, a == 0 ? b : c |
| Conditional AND/OR | &&, || |
Examples
Section titled “Examples”Constant calculation
Section titled “ Constant calculation”(4 + 2) * 3
This will return a time series of constant value 18.
Addition with constant
Section titled “ Addition with constant”[id:462] + 4
This will return the data of time series with id 462 but each data point is incremented by 4.
Addition with constant data point
Section titled “ Addition with constant data point”[id:462] + {4|19}
This will return the data of time series with id 462 but each data point is incremented by 4 using the flag 19 (Missing).
Modulo operation
Section titled “ Modulo operation”[id:462].Value % 4
This will return the remainder of each data point of the time series with id 462 divided by 4.
Conditional value replacement
Section titled “ Conditional value replacement”[TemperatureSeries].Value < 10 ? 0 : [TemperatureSeries].Value
This will return a time series where each value lower than 10 is set to 0 with the default flag.
For a full list of supported operations look up Operators and expressions - C# reference.
Custom Functions
Section titled “Custom Functions”Custom functions can be used in formulas with the following syntax:
<FunctionName>(<parameters>)A custom function is calculation logic of your own, written as a .NET class and made available to PowerTSM either as a file in the custom functions folder or by registering it in the PowerTSM .NET API. What such a function can receive from a formula, how its result is flagged, and when it is executed is described in Custom Functions.