Equations are great for combining values from other places in Spider Impact. They're used throughout the software for things like calculated KPI values, building dataset fields, and adding advanced filters to reports, charts, and dashboards.
Referencing Other Values
There are several types of values you can reference in equations. KPI values can only be used in calculated KPI equations. They're referenced by scorecard item ID like this:
M(123)
Scorecard item scores are the same kind of thing. They're only used in KPI equations, and they're referenced by scorecard item ID like this:
S(123)
Similarly, initiative values are also only used in calculated KPI equations. They're referenced by initiative ID and field like this:
I(BR, 123)
Dataset values are different. They can only be used in calculated dataset fields and filters. They're referenced by field name like this:
[Incident Date]
This is just the start of what you can do with references to other values. Please see the the Calculated KPIs and Dataset Equations: Fields and Filters articles for more information.
If/Else
The syntax for an IF statement is:
if(condition, truevalue, falsevalue)
Here's an example equation. "If the value for KPI #123 is 5, this equation returns 10. Otherwise return 0."
if(M(123) == 5, 10, 0)
Note that you'll need to use the double equal operator == when checking for an equal value, as explained below.
You can also string together multiple IF statements to create an IF/ELSE chain like this. "If the value for KPI #123 is 5, return 10. Else if the value for KPI #123 is 4, return 100. Else return 0."
if(M(123) == 5, 10, if( M(123) == 4, 100, 0))
Text (String) Manipulation
You can concatenate text together with the + symbol, and you can reference specific text in quotes. For example, here's how you'd create a new text string that is the first name field, then a space, then the last name field:
[First Name] + " " + [Last Name]
You can also do text manipulation on numbers if you first tell the software to treat the number like text. In this example, we want to get the first four characters of a number. To do this, we first have to concatenate the number with blank text to turn it into text.
left(""+[myNumber],4)
Yes/No KPI Values
Yes/No values can be referenced as booleans (true/false) or as numbers (1/0).
In this example, we're building an equation for a number KPI, and we're using the value from a Yes/No KPI in that equation. "If the value for KPI #123 is yes, return 5. Else return 20"
if(M(123), 5, 20)
It goes the other way too. In this example, we're building an equation for a Yes/No KPI, and we're using the value from a number KPI in that equation. "If the value for KPI #456 is greater than 7 return true. Else return false"
if(M(456) > 7, true, false)
This is the same as:
if(M(456) > 7, 1, 0)
Note that in the example above we're using 1 and 0, but any nonzero number will evaluate to Yes in a Yes/No KPI's equation.
Because Yes/No KPI values are treated as 1 and 0, you can even use them in functions just like any other KPI value. In this example we're building an equation for a calculated Yes/No KPI. This equation looks at three other Yes/No KPIs. If most of them are yes, it returns yes. If most are no, it returns no.
if(avg(M(123), M(456), M(789)) > 0.5, true, false)
Dates
You can adjust a date by a certain number of days using the plus and minus operators (+ and ). For example, this means 5 days after the incident date:
[Incident Date] + 5
and this means 5 days before the incident date:
[Incident Date]  5
You can adjust a date by years, months, or days by using the add() and subtract() functions. For example, this would be three months after the incident date.
add([Incident Date], 3, "months")
and this would be one year before the incident date:
subtract([Incident Date], 1, "years")
You can reference specific attributes of a date by using the month(), year(), dayofweek(), dayofmonth(), and dayofyear() functions. Months are returned as 1 (for January)  12 (for December) and days of the week return 1 (for Sunday)  7 (for Saturday). For example, if the incident rate for a record were on July 1st, 2022, this would return a value of 7:
month([Incident Date])
And this would return 184:
dayofyear([Incident Date])
To reference the current date, use the today() function:
today()
You can parse dates from strings that are in ISO8601 format with the date() function. For example, this evaluates to December 31st, 2018:
date(20181231)
If the date string isn't in ISO8601, you can tell Impact how to parse the dates with Y, M, and D characters. If mydate were formatted like 3/15/2020 you'd use:
date(mydate, 'm/d/y')
If mydate were formatted like 15Mar20 you'd use:
date(mydate, 'dmy')
If mydate were formatted like March 15, 2020 you'd use:
date(mydate, 'm d, y')
Like all functions in equations, you can combine multiple date functions together. For example, here's how to determine the number of days in the current year:
dayofyear(year(today())+"1231")
Operators
Spider Impact supports a wide variety of operators in equations.
Operator 
Symbol 
Addition, Subtraction 
+,  
Multiplication 
* 
Division 
/ 
Not Equal, Equal 
!=, == 
Power 
^ 
Boolean Not 
! 
Unary Plus, Unary Minus 
+x, x 
Modulus (remainder) 
% 
Less Than, Greater Than 
<, > 
Less or Equal, More or Equal 
<=, >= 
Boolean & 
&& 
Boolean Or 
 
Functions
This is a comprehensive list of all functions available in Spider Impact. Please see the Calculated KPIs article for more information and examples about using the most popular functions.
Spider Impact Functions 
Format 
Notes 
Empty (blank, null) value check 
isblank(kpi_id) 

N/A (not applicable) value check 
isna(kpi_id) 
KPIs only 
KPI's own value 
M() 
KPIs only 
KPI's own threshold 
T(field) 
KPIs only 
KPI's own value in another period (three earlier) 
M(3p) 
KPIs only 
KPI's own threshold in another period 
T(field, 3p) 
KPIs only 
another KPI value 
M(kpi_id) 
KPIs only 
another KPI threshold 
T(kpi_id, field) 
KPIs only 
another KPI value in another period 
M(kpi_id, 3p) 
KPIs only 
another KPI threshold in another period 
T(kpi_id, field, 3p) 
KPIs only 
another scorecard item score 
S(item_id) 
KPIs only 
another scorecard item score in another period 
S(item_id, 3p) 
KPIs only 
initiative item's value (see above for list of fields) 
I(field, initiative_id) 
KPIs only 
Todate aggregation (Sum or Average) 
TD(calendar, kpi_id, field, aggregation) 
KPIs only 
Text (String) Functions 
Format 
Concatenation 
mystring1 + mystring2 
Left (first 4 characters) 
left(mystring, 4) 
Right (last 4 characters) 
right(mystring, 4) 
Middle (3character string starting at the second character) 
mid(mystring, 2, 3) 
Substring (Starting at the second character and ending at the third) 
substr(mystring, 2, 3) 
Substring (Everything starting at the second character) 
substr(mystring, 2) 
Lower Case 
lower(mystring) 
Upper Case 
upper(mystring) 
Length 
len(mystring) 
Trim Whitespace 
trim(mystring) 
Date Functions 
Format 
Day Addition, Day Subtraction 
+,  
Add months, days, or years 
add(mydate, 3, "months") 
Subtract months, days, or years 
subtract(mydate, 2, "years") 
Month [1 (January) to 12 (December)] 
month(mydate) 
Year 
year(mydate) 
Day of the week [1 (Sunday) to 7 (Saturday)] 
dayofweek(mydate) 
Day of the month [1 to 31] 
dayofmonth(mydate) 
Day of the year [1 to 365] 
dayofyear(mydate) 
Current date 
today() 
Date parse (ISO8601) 
date(mydate) 
Date parse (example, March 15, 2020) 
date(mydate, 'm d, y') 
Statistical Functions 
Format 
Average (ignores blanks) 
avg(x1, x2, x3, …) 
Sum 
sum(x1, x2, x3, …) 
Minimum (ignores blanks) 
min(x1, x2, x3, …) 
Maximum (ignores blanks) 
max(x1, x2, x3, …) 
Rounding Functions 
Format 
Round (round up when tied) 
round(x), round(x, decimal_places) 
Round (round to even value when tied) 
rint(x), rint(x, decimal_places) 
Floor 
floor(x) 
Ceiling 
ceil(x) 
Other Common Functions 
Format 
Str (convert number to a string) 
str(x) 
Absolute Value / Magnitude 
abs(x) 
Random Number (between 0 and 1) 
rand() 
Modulus (remainder when x is divided by y) 
mod(x,y) 
Square Root 
sqrt(x) 
Binomial coefficients 
binom(n, i) 
Signum (1,0,1 depending on sign of argument) 
signum(x) 
Trigonometric Functions 
Format 
Sine 
sin(x) 
Cosine 
cos(x) 
Tangent 
tan(x) 
Arc Sine 
asin(x) 
Arc Cosine 
acos(x) 
Arc Tangent 
atan(x) 
Arc Tan with 2 parameters 
atan2(y, x) 
Secant 
sec(x) 
Cosecant 
cosec(x) 
Cotangent 
cot(x) 
Hyperbolic Sine 
sinh(x) 
Hyperbolic Cosine 
cosh(x) 
Hyperbolic Tangent 
tanh(x) 
Inverse Hyperbolic Sine 
asinh(x) 
Inverse Hyperbolic Cosine 
acosh(x) 
Inverse Hyperbolic Tangent 
atanh(x) 
Log and Exponential 
Format 
Natural Logarithm 
ln(x) 
Logarithm base 10 
log(x) 
Logarithm base 2 
lg(x) 
Exponential (e^x) 
exp(x) 
Power 
pow(x) 
Always use "." for decimal and "," for functions
Many European languages use the "," character for the decimal separator and "." for the thousands separator. For everywhere except equations, Spider Impact looks at your browser's language settings and correctly displays numbers based on your region.
Equations in Spider Impact are different. Regardless of your language settings, you'll need to use "." for decimal separators and "," for separating function arguments. This is common in programming languages and allows you to build advanced equations in Spider Impact that are used across regions.