This reference guide is for calculated items. Calculations in boards has it's own guide and is used inside the product with access to additional functions.
Calculated items take a set of variables, applies an aggregation to each of the input variables to align them in time and finally applies a formula that returns a single value for each timestamp. If we break this down, we can start defining what a calculated item will look like.
To create a new calculated item, you will need to be an administrator of your organization.
A calculated item contains three basic components, a set of variables, a formula and an aggregation setting.
The input to a calculated item is its variable(s), where each variable is an existing item in Clarify, these provide us with the data to perform the calculation on. When we choose the items we want to use, each one gets assigned a variable name that we can use when referring to it in our formula.
The aggregation for a calculated item is applied to all the variables, and is defined by a time period, and an aggregation method. Examples of this could be
sum(1 hour) or
When we perform our calculation, we will take the value of each variable, at each available timestamp, as input. Since items don't necessarily have timestamps that are exactly the same, let's consider what would happen both when they do and when they don't.
Timestamps exactly overlap
For items where the timestamps perfectly overlap, there is no need to aggregate the data before performing calculations. We simply want to take the value of each item, at each timestamp, and perform our calculation.
When defining calculated items in Clarify, you need to specify an aggregation. But what should we do in cases where we know that the timestamps align, and we want to preserve the original resolution of the data? In these cases, we can simply set the aggregation to be a higher resolution than the original data.
As an example, if you want to add together two items where values are stored once per hour, setting the aggregation time to once per minute will result in keeping the original resolution.
Timestamps don't overlap
If the timestamps don't overlap we can use aggregation to re-align them in a way that makes us able to perform the calculation we want.
Let's also consider one last case that we can use aggregation time for, which is down-sampling our data prior to performing calculations. This happens if we set the aggregation time high enough that it covers more than one sample for each variable. In that case, multiple samples for each variable will be aggregated together, and the result of that will be used for the calculation.
The formula tells Clarify what to do with the values for each time stamp after the aggregation step.
For mathematical operations, the following operators are currently supported:
**- power of
count(...)- The number of non-empty inputs for all timestamps where there is at least one non-empty input.
min(...)- minimum of all non-empty inputs for all timestamps where where there is at least one non-empty entry.
max(...)- maximum of all non-empty inputs for all timestamps where where there is at least one non-empty entry.
sum(...)- sum of all non-empty inputs for all timestamps where there is at least one data non-empty entry.
avg(...)- average of all non-empty inputs for all timestamps where where there is at least one non-empty entry.
sinh(x)- hyperbolic sine
asin(x)- arcsine / inverse sine
asinh(x)- hyperbolic arcsine
cosh(x)- hyperbolic cosine
acos(x)- arccosine / inverse cosine
acosh(x)- hyperbolic arccosine
tanh(x)- hyperbolic tangent
atan(x)- arctangent / inverse tangent
atanh(x)- hyperbolic arctangent
atan2(x,y)- arctangent of two numbers / four-quadrant inverse tangent
round(x)- round to nearest integer
floor(x)- round down to closest integer
ceil(x)- round up to closest integer
erf(x)- the (Gauss) error function
erfinv(x)- the inverse of the (Gauss) error function
erfc(x)- the complimentary (Gauss) error function
erfcinv(x)- the inverse of the complementary (Gauss) error function
Other math functions:
sqrt(x)- square root
log(x)- the natural logarithm of x
log1p(x)- the natural logarithm of 1+x with higher precision for small values of x
log2(x)- the base 10 logarithm of x
log10(x)- the base 10 logarithm of x
gamma(x)- the Gamma function
A single time function is available to calculated items:
time_seconds()- return the number of seconds since the UNIX epoch with floating point precision
Note that in calculated items the
time_seconds() function only return a result when at least one other item contains a value. This is different from how it works for the
clarify.evaluate API call and for calculations in Boards.
We also support conditional operations, and a ternary syntax which supports nesting for fine-grained control over evaluation:
<condition> ? <if true> : <if false>- ternary condition
<condition> ? <if true>- ternary condition with only if true parameter
<condition> ?- ternary condition without any parameters; return
1if true or
<value> ?? <if null>- null coalesce function; replace omitted (
NULL) values with if value.
>- larger than
<- smaller than
>=- larger than or equal
<=- smaller than or equal
By default, division and multiplication is evaluated before addition and subtraction. To explicitly control the order of evaluation, you use parenthesis.
- Add together three variables
a + b + c.
- Calculate average of two variables
(a + b) / 2
- Multiply by a constant
a * 10
- Calculate a to the power of 2
- Calculate a complicated KPI
sqrt(a) + (b / (d * 2))
- Compare two variables and return the lowest
a < b ? a : b
- Compose multiple conditions to create an enum
a > 10 ? (a > 20 ? 2 : 1) : 0
- Check if a is equal to 10, if is return 1, otherwise return 0
a == 10 ? 1 : 0
- Check if a is larger than b, if so return a, otherwise return b
a > b ? a : b
- Check if a is larger than 10, if so check if it's also larger than 20. If larger than 20, return 2, if larger than 10 return 1, otherwise return 0
a > 10 ? (a > 20 ? 2 : 1) : 0
- Check if a is larger than 5 and b is equal to 2, if so return true, otherwise return false
a > 5 && b == 2
The following constants can be used in calculations. Constants use capital letters and have 62 significant digits.
E- Euler's number
PI- Archimedes's constant
PHI- The golden ratio
On 2023-08-25 we deployed a deliberate breaking change that alters the omit handling for the add (
+) and subtract (
Even with aggregation there will be cases where not all variables in a calculation will have values at every timestamp. In these situations, it's important to understand what Clarify will do with the output values.
Clarify standard operators only produce a result when both inputs are present. In other words, if at least one of the input values are omitted, the result wil be omitted as well. In addition, operations that returns undefined results, such as divisions by zero, will return omitted values as well. We do however allow operations to combine values with omitted values by using either ternary conditions or the
Below is a list of the potential situations that can arise, as well as the result that will be returned.
0 / 0 = omitted
omitted / X = omitted
X / 0 = omitted
X / omitted = omitted
X * omitted = omitted
X + omitted = omitted
X - omitted = omitted
omitted + X = omitted
omitted - X = omitted
X == null ? X : 0return
sum(omitted, omitted) = omitted
sum(X, omitted) = X
sum(omitted, X) = X
sum(omitted, -X) = -X
sum(omitted, X, Y) = X+Y
count(omitted, X, Y) = 2
avg(omitted, X, Y) = (X+Y)/2