07 – Examples

@Var is the name of the generic variable.

Some simple KPIs

Conditional expressions (CASE and IIF)

Example 1:

CASE @Var
    WHEN 30 THEN 1
    ELSE 0
END;

The same formula can be written as:

IIF(@Var = 30, 1, 0);

Example 2:

CASE
    WHEN @Var = 30 THEN 1
    WHEN @Var = 60 THEN 2
    WHEN @Var >= 90 THEN 3
    ELSE 0
END;

AND and OR logical operators

CASE
    WHEN (@Var1 = 1) OR (@Var2 = 50 AND @Var3 = 100) THEN 1
    ELSE 0
END;

Nested conditional expressions (CASE inside a CASE)

CASE
    WHEN @Var1 = 0 THEN 0
    WHEN @Var1 < 10 THEN
        CASE
            WHEN @Var2 = 100 THEN 1000
            ELSE 2000
        END
    ELSE
        CASE
            WHEN @Var2 = 200 THEN 2000
            ELSE 4000
        END
END;

Correlation Coefficient

@@CALCULATE_EVERY = 15 MINUTES;
@@DATA_COVERAGE = 1 HOUR;
hstats.cor(@Var1, @Var2);

Average

@@CALCULATE_EVERY=1 HOUR;
@@DATA_COVERAGE=1 HOUR;
hstats.avg(@Var);

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.avg(IIF(@Status = 1, @Var, NULL);

Comment: null values are discarded when calculating aggregated functions.

Average ratio over different periods

@@CALCULATE_EVERY = 1 WEEK;
@@DATA_COVERAGE = 2 DAYS;
@@DATA2_COVERAGE = 2 DAYS;
@@DATA2_TIMESHIFT = 1 WEEKS;
hstats.avgratio(@Var);

Slope

stats.slope(@Var, 1 HOUR);

Exponential moving average

stats.ema(@Var,0.5);

OR

CASE
WHEN 1 IN (@Var1, @Var2, @Var3)
THEN 1
ELSE 0
END;

Time slots

Check date and time of the value’s timestamp in order to calculate the formula.

@@TIMESTAMP_POSITION = END;
@@CALCULATE_EVERY = 1 MINUTE;
CASE   
    WHEN @Var = 1 THEN
        CASE
            WHEN CAST(@@EndOfPeriod_local AS TIME) BETWEEN ’00:00:00′ AND ’07:30:00′ THEN 1
            WHEN CAST(@@EndOfPeriod_local AS TIME) BETWEEN ’08:00:00′ AND ’16:30:00′ THEN 1
            WHEN CAST(@@EndOfPeriod_local AS TIME) >= ’17:00:00′ THEN 1
            ELSE 0
        END
    ELSE 0
END;

Comment: the KPI is set to 1 when the time of calculation is between midnight and 7:30 AM, between 8:00 AM and 16:30 AM and between 17:00:00 and midnight, otherwise is set to 0. All this, considering the local time zone (the plant time zone)

Average with condition

We want to calculate the average of @Var tag, only when @Status tag is 4.
NOTE: null values are discarded when calculating aggregated functions.

@@CALCULATE_EVERY = 1 DAY;
@@DATA_COVERAGE = 1 DAY;
hstats.avg(
    CASE
            WHEN @Status = 4 THEN @Var
            ELSE NULL
    END
);

Digital time counter

The KPI counts the sum of time the @Var tag has been in ON (1 value) condition, in seconds.

@@CALCULATE_EVERY = 2 HOURS;
hstats.digitaltime_on(@Var);

Bitmask

CASE
    WHEN @Var & 0x00 > 0 THEN 1
    WHEN @Var & 0x01 > 0 THEN 2
    ELSE 0
END;

Sum of increments

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

Threshold exceeding counter

This formula counts the number of times a tag @Var value surpasses a certain threshold (10000 in this example):

@@SKIP_WHEN_NULL = 1;
CASE
    WHEN @@PreviousValue(@Var) < 10 AND @Var >= 10 THEN ISNULL(@@THIS, 0) + 1
    ELSE NULL
END;

This formula counts the number of times the tag @Var value returns within that threshold:

@@SKIP_WHEN_NULL = 1;
CASE
    WHEN @@PreviousValue(@tagCounter) >= 10 AND @tagCounter < 10 THEN ISNULL(@@THIS, 0) + 1
    ELSE NULL
END;

Was this helpful?

YesNo

0 / 0