06 – Periodic KPIs – Rilheva Wiki

Special tags

In addition to the @tagName and @metadataName tags (see the “Common tags and special between Live and Periodic KPIs” section), the following values are available for use in formulas

@@StartOfPeriod_UTC
@@StartOfPeriod_local

These tagsrepresent the timestamp of the start of the calculation period, in UTC and in the plant timezone, respectively.

Example:

@@CALCULATE_EVERY = 1 HOUR;
CASE
WHEN DATEPART(HOUR, @@StartOfPeriod_local) = 1 THEN hstats.max(@consumoEnergia)
ELSE 0
END;

In this example, the only period of the day in which the KPI is calculated as “hstats.max(@consumoEnergia)” is between 01:00:00 and 02:00:00, local time, as @@StartOfPeriod_local is 01: 00:00 and therefore the condition “DATEPART(HOUR, @@StartOfPeriod_local) = 1” is true.

@@EndOfPeriod_UTC
@@EndOfPeriod_local

These tags represent the timestamp of the end of the calculation period, in UTC and in the plant timezone, respectively.

NOTE:

The end of the period coincides with the start of the subsequent period; for example, with:

@@CALCULATE_EVERY = 1 HOUR;

when the KPI is calculated at 01:00:00 in local time, the period between 00:00:00 and 01:00:00 is therefore calculated, and @@EndOfPeriod_local is set to 01:00:00, and not to 00:59:59.

Example:

@@CALCULATE_EVERY = 1 HOUR;
CASE
WHEN DATEPART(HOUR, @@EndOfPeriod_local) = 1 THEN hstats.max(@energyTot)
ELSE 0
END;

In this example, the only period in which the KPI is calculated as “hstats.max(@energyTot)” is between 00:00:00 and 01:00:00 local, as @@EndOfPeriod_local is 01: 00:00 and therefore the condition “DATEPART(HOUR, @@StartOfPeriod_local) = 1” is true.

Directives

In addition to @@SKIP_WHEN_NULL and @@RECALCULATE_FROM ((see the “Common directives between Live KPIs and Periodic KPIs” section), the following directives are available.

@@CALCULATE_EVERY = N MINUTE/HOUR/DAY/WEEK/MONTH(S);

This directive indicates that the KPI is not calculated live, but on a periodic basis.

Without this directive, the formula is calculated “live”, i.e. with each change of alt least one of the tags that are part of it.

Example of periodic KPI:

@@CALCULATE_EVERY = 15 MINUTES;
(@temperature – 32) × 5/9;

In this case the calculation is run every 15 minutes.
In case of a “simple” formula, which is, withoutcustom Rilheva functions, the values of all the tags in the formula are taken at the end of the calculation period.

@@TIMESTAMP_POSITION = START/MID/ADJUSTED_END/END;

It can be omitted, in which case the default setting is ADJUSTED_END.

This directive indicates which rule is applied to assign the timestamp of the periodic KPI:

START: the timestamp is positioned at the beginning of the coverage period.
MID: The timestamp is positioned in the middle of the coverage period.
ADJUSTED_END: The timestamp is positioned at the end of the coverage period, with an adjustment of -1 second.

For example, if the coverage is 1 hour and the period from 00:00:00 to 1:00:00 is processed, the timestamp with ADJUSTED_END is set to 00:59:59.

END: The timestamp is positioned at the end of the calculation period.

Example:

@@CALCULATE_EVERY = 1 DAY;
@@TIMESTAMP_POSITION = START;
hstats.avg(@temperature);

At 00:00:00 on January 2nd, the average for the whole day of January 1st is calculated. The timestamp is set as follows:
START→ January 1st 00:00:00
MID→ January 1st 12:00:00
ADJUSTED_END→ January 1st 11:59:59 PM
END → January 2nd 00:00:00

@@DATA_COVERAGE = N MINUTE/HOUR/DAY/WEEK/MONTH(S);

Sets the coverage period for the unique/first data-set used by the custom functions in the formula.

The coverage has as:

start = calculation time – @@DATA_COVERAGE
end = calculation time

NOTE: if omitted, @@DATA_COVERAGE takes the same value of @@CALCULATE_EVERY.

@@DATA2_COVERAGE = N MINUTE/HOUR/DAY/WEEK/MONTH(S);
@@DATA2_TIMESHIFT = N MINUTE(S)/HOUR(S)/DAY(S)/WEEK(S)/MONTH(S);

See @@DATA_COVERAGE.

Sets the coverage period for the second data-set used by the custom functions in the formula.

If the function needs a second data-set (see hstats.avgsratio for example), @@DATA2_COVERAGE and @@DATA2_TIMESHIFT are required.

The coverage has as:

start = calculation time – @@DATA2_TIMESHIFT – @@DATA2_COVERAGE
end = calculation time – @@DATA2_TIMESHIFT.

The piece of formula “hstats.avgratio(@temperature)” is calculated as follows:
(average of the last two days) / (average of the same two days, one week earlier)
which is equal to:
(average from Saturday 8 at 00:00 to Sunday 9 at 24:00) / (average fromSaturday 1 at 00:00 to Sunday 2 at 24:00)

Custom functions

In periodic KPIs it is possible to use the following custom Rilheva functions, in addition to all the functions available in Transact SQL language..

hstats.avg(arg0)

Calculates the arithmetic, not weighted average, within the period.
The arguments are:

arg0: it can be a simple variable, or a Transact SQL formula.

Example:

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.avg(@activePower);

The average of the active power is calculated.

hstats.avgratio(arg0)

Calculates the ratio between the averages of two different periods.
The arguments are:

arg0: it can be a simple variable, or a Transact SQL formula.

Example:

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 2 DAYS;
@@DATA2_COVERAGE = 2 DAYS;
@@DATA2_TIMESHIFT = 1 WEEK;
hstats.avgsratio(@temperature) * 1000;

For example, the daily calculation (@@CALCULATE_EVERY = 1 DAY) is running at 00:00 of January, Monday 10.
The piece of formula “hstats.avgratio(@temperature)” is calculated as follows:
(average of the last two days) / (average of the same two days, one week earlier)
which is equal to:
(average from Saturday 8 at 00:00 to Sunday 9 at 24:00) / (average fromSaturday 1 at 00:00 to Sunday 2 at 24:00)

hstats.cor(arg0, arg1)

Pearson correlation coefficient.
Indicates whether there can be a correlation between the two arguments, which can be positive or negative.
If positive, as arg0 increases, arg1 increases, if negative, as arg0 increases, arg1 decreases and vice versa.
As a rough guide, a value of 0.7 (or -0.7) or higher indicates a strong correlation between the two arguments.
The arguments are:

arg0: it can be a simple variable or a Transact SQL formula
arg1: it can be a simple variable or a Transact SQL formula

NOTE: the timestamp of the data points is taken into consideration to perform the correlation. To correlate data it is therefore necessary that arg0 and arg1 have exactly the same timestamps.

Example:

@@CALCULATE_EVERY = 1 HOUR;
@@DATA_COVERAGE = 1 HOUR;
hstats.cor(@temperature, @consumption);

hstats.digitalcycles_count(arg0)

Calculates the times a digital signal passes from OFF to ON.
The arguments are:

arg0: it can be a simple variable or a Transact SQL formula. In order for the calculation to be meaningful, arg0 should resolve just to 0 or 1 values, such as a digital tag.

Example:

@@CALCULATE_EVERY = 1 HOUR;
@@DATA_COVERAGE = 1 HOUR;
hstats.digitalcycles_count(@engineStarted);

hstats.digitaltime_off(arg0)

Calculates the time a digital signal remains OFF, in seconds.
The arguments are:

arg0: it can be a simple variable or a Transact SQL formula. In order for the calculation to be meaningful, arg0 should resolve just to 0 or 1 values, such as a digital tag.

Example:

@@CALCULATE_EVERY = 1 HOUR;
@@DATA_COVERAGE = 1 HOUR;
hstats.digitaltime_off(@engineOn);

hstats.digitaltime_on(arg0)

Calculates the time a digital remains ON, in seconds.
The arguments are:

arg0: it can be a simple variable or a Transact SQL formula. In order for the calculation to be meaningful, arg0 should resolve just to 0 or 1 values, such as a digital tag.

Example:

@@CALCULATE_EVERY = 1 HOUR;
@@DATA_COVERAGE = 1 HOUR;
hstats.digitaltime_on(@engineOn);

hstats.increment(arg0)

Calculates the increase that the argument has had in the period, as the difference between the value at the end of the period and the value at the beginning of the period.
If the calculation is negative, it is forced to zero.
The arguments are:

arg0: it can be a simple variable, or a Transact SQL formula.

NOTE: when calculating the value at the beginning of period, if a tag part of arg0 doesn’t have a data point in that exact time, the last value of the previous period is taken into account.

Example:

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.increment(@totalEnergy);

Starting from the total energy produced, which is an incremental counter, this formula calculates the production in one day.

hstats.integral(arg0, seconds_for_one_unit)

Performs the integral calculation.
The arguments are:

arg0: it can be a simple variable, or a Transact SQL formula.
seconds_for_one_unit: states how many seconds must pass, with arg0 at 1, to accumulate one unit.

For example, if arg0 is in kW, and you want to calculate the integral in kWh, this argument must be set to 3600, because 3600 seconds must pass with arg0 at 1 to accumulate 1 kWh.

NOTE: when calculating the value at the beginning of period, if a tag part of arg0 doesn’t have a data point in that exact time, the last value of the previous period is taken into account.

Example:

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.integral(@activePower, 3600);

This formula calculates electricity production in a day, in kWh, based on an active power tag, expressed in kW.

hstats.last_minus_first(arg0)

Calculates the difference between the first and last datapoints within the period.
The arguments are:

arg0: it can be a simple variable, or a Transact SQL formula.

NOTE: the tag data points involved in the calculation are exclusively taken from the calculation period, so no evaluations are made whether there are data points available at the exact timestamp of the beginning of period or not. Its behavior is therefore different than other functions, like hstats.increment or hstats.range.

Example:

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.last_minus_first(@Totalenergy);

We have the total energy produced, which is an incremental counter, and this formula calculates the production in one day.
Actually, in this case it would be more appropriate to use the hstats.increment or hstats.range function, because hstats.increment and hstats.range also take into consideration the pending variation between one period and the next.

hstats.max(arg0)

Calculates the maximum value within the period.
The arguments are:

arg0: it can be a simple variable, or a Transact SQL formula.

Example:

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.max(@ActivePower);

This formula returns the maximum peak active power during the day.

hstats.min(arg0)

Calculate the minimum value within the period.
The arguments are:

arg0: it can be a simple variable, or a Transact SQL formula.

Example:

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.max(@activePower);

This formula returns the lowest value of the active power during the day.

hstats.range(arg0)

Calculates the range, as absolute value, between the minimum and maximum value of the period.
The arguments are:

arg0: it can be a simple variable, or a Transact SQL formula.

hstats.increment subtracts the value at the beginning of the period from the last value of the period.
hstats.range subtracts the maximum in the period from the minimum in the period.

Therefore, if arg0 is not a value that necessarily continues to increase, but can also have negative variations, the result of the two functions can be different.

Example:

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.range(@totalEnergy);

This formula returns the partial energy produced during the day, as the difference between the minimum and maximum of the datapoints in the period.
In particular, if there is no datapoint excactly at 00:00:00 of the current day, the last datapoint of the previous day is considered as part of the period..
– 2020-01-01 23:59:00: total energy: 4990 kWh
– 2020-01-02 00:01:00: total energy: 5000 kWh
…
– 2020-01-02 23:59:00: total energy: 5200 kWh
Considering what was previously said about the calculation behavior, for the day 2020-01-02 we have a range of 210.
If we used hstats.last_minus_first instead, we would have gotten 200 as result, and the 10 kWh of production between 11:59:00 PM the previous day and 12:00 AM: 01:00 of the current day would never be counted, neither in the KPI of January 1st nor in that of January 2nd.

hstats.stdev(arg0)

Calculates the standard deviation of the argument, over the period.
The arguments are:

arg0: it can be a simple variable, or a Transact SQL formula.

Example:

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.var(@Instant Consumption);

hstats.sum_of_differences(argValue[, argCondition])

Calculates the sum of the differences of argValue over the period. If argCondition is defined, the difference on a timestamp is added only if argCondition, in that timestamp, is true.
Both positive and negative differences are summed.
The arguments are:

argValue: it can be a simple variable, or a Transact SQL formula.
argCondition: Optional. It can be a simple variable, or a Transact SQL formula. As it is treated as digital switch on/off, it must resolve just to 0 or 1 values.

NOTE: when calculating the value at the beginning of period, if a tag part of arg0 doesn’t have a data point in that exact time, the last value of the previous period is taken into account.

Examples:

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.sum_of_differences(@totEnergy);

In this example, argCondition is not set, so every difference, throughout the day, is summed.

2020-01-01 23:59:00: tot energy: 4990 kWh
This is considered as the initial value of the period, as we don’t have an exact data point at 00:00:00
2020-01-02 00:01:00: tot energy: 5000 kWh → 5000 – 4990 = 10
2020-01-02 08:00:00: tot energy: 5100 kWh → 5100 – 5000 = 100
2020-01-02 16:00:00: tot energy: 5150 kWh → 5150 – 5100 = 50
2020-01-02 23:59:00: tot energy: 5220 kWh → 5220 – 5150 = 70

Therefore, the KPI for the 2nd of January 2020 is 10 + 100 + 50 + 70 = 230.

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.sum_of_differences(@totEnergy, @productionOn);

In this example, the single differences during the day are summed just when @productionOn is 1.

2020-01-01 23:59:00: tot energy: 4990 kWh
This is considered as the initial value of the period, as we don’t have an exact data point at 00:00:00
2020-01-02 00:01:00: tot energy: 5000 kWh, @productionOn: 1 → 5000 – 4990 = 10
2020-01-02 08:00:00: tot energy: 5100 kWh, @productionOn: 0 → 5100 – 5000 = 100
2020-01-02 16:00:00: tot energy: 5150 kWh, @productionOn: 0 → 5150 – 5100 = 50
2020-01-02 23:59:00: tot energy: 5220 kWh, @productionOn: 1 → 5220 – 5150 = 70

Therefore, the KPI for the 2nd of January 2020 is 10 + 70 = 80.

hstats.sum_of_increments(argValue[, argCondition])

Calculates the sum of the differences of argValue over the period. If argCondition is defined, the difference on a timestamp is added only if argCondition, in that timestamp, is true.

Negative differences are summed as zero. Therefore, if we have an incremental counter having an auto-reset feature, when it resets, for example from 10000 to 0, an increment of -10000 is not calculated, but is counted at zero.

The arguments are:

argValue: it can be a simple variable, or a Transact SQL formula.
argCondition: Optional. It can be a simple variable, or a Transact SQL formula. As it is treated as digital switch on/off, it must resolve just to 0 or 1 values.

NOTE: when calculating the value at the beginning of period, if a tag part of arg0 doesn’t have a data point in that exact time, the last value of the previous period is taken into account.

Examples:

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.sum_of_increments(@totalEnergy);

In this example, argCondition is not set, so every positive increment, throughout the day, is summed.

2020-01-01 23:59:00: tot energy: 4990 kWh
This is considered as the initial value of the period, as we don’t have an exact data point at 00:00:00
2020-01-02 00:01:00: tot energy: 5000 kWh → 5000 – 4990 = 10
2020-01-02 08:00:00: tot energy: 0 kWh → 0 – 5000 = forced to zero
2020-01-02 16:00:00: tot energy: 50 kWh → 50 – 0 = 50
2020-01-02 23:59:00: tot energy: 120 kWh → 120 – 50 = 70

Therefore, the KPI for the 2nd of January 2020 is 10 + 50 + 70 = 130.

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.sum_of_increments(@totalEnergy, @productionOn);

In this example, the positive increments during the day are summed just when @productionOn is 1.

2020-01-01 23:59:00: tot energy: 4990 kWh
This is considered as the initial value of the period, as we don’t have an exact data point at 00:00:00
2020-01-02 00:01:00: tot energy: 5000 kWh, @productionOn: 1 → 5000 – 4990 = 10
2020-01-02 08:00:00: tot energy: 0 kWh → 0 – 5000 = forced to 0
2020-01-02 16:00:00: tot energy: 50 kWh, @productionOn: 1 → 50 – 0 = 50
2020-01-02 23:59:00: tot energy: 120 kWh, @productionOn: 0 → 120 – 50 = 70

Therefore, the KPI for the 2nd of January 2020 is 10 + 50 = 60.

hstats.var(arg0)

Calculation of the variance of the argument, in the period.
The arguments are:

arg0: it can be a simple variable, or a Transact SQL formula.

Example:

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.var(@instantConsumption);

Was this helpful?

0 / 0

06 – Periodic KPIs