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 non-zero 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 determine the number of days between two date fields using subtraction. For example, this would be the number of days between the incident date and the report date:

[Report Date] - [Incident Date]

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 ISO-8601 format with the date() function. For example, this evaluates to December 31st, 2018:

date("2018-12-31")

If the date string isn't in ISO-8601, 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 15-Mar-20 you'd use:

date(mydate, "d-m-y")

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())+"-12-31")

Operators

Spider Impact supports a wide variety of operators in equations.

Arithmetic Operators

Operator	Symbol	Description
Addition	+	Adds two values
Subtraction	-	Subtracts the right value from the left value
Multiplication	*	Multiplies two values
Division	/	Divides the left value by the right value
Power	^	Raises the left value to the power of the right value
Modulus	%	Returns the remainder when the left value is divided by the right value

Comparison Operators

Operator	Symbol	Description
Equal	==	Checks if two values are equal
Not Equal	!=	Checks if two values are not equal
Less Than	<	Checks if the left value is less than the right value
Greater Than	>	Checks if the left value is greater than the right value
Less Than or Equal	<=	Checks if the left value is less than or equal to the right value
Greater Than or Equal	>=	Checks if the left value is greater than or equal to the right value

Logical Operators

Operator	Symbol	Description
Boolean AND	&&	Returns true if both operands are true
Boolean OR	\|\|	Returns true if at least one operand is true
Boolean NOT	!	Inverts the boolean value of its operand

Unary Operators

Operator	Symbol	Description
Unary Plus	+x	Represents the identity of x
Unary Minus	-x	Negates the value of x

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 in KPI equations, and the Dataset Equations: Fields and Filters article for information and examples about using dataset equations.

Calculated KPI Functions	Format
Empty (blank, null) KPI value check	isblank(kpi_id)
N/A (not applicable) KPI value check	isna(kpi_id)
KPI's own value	M()
KPI's own threshold	T(field)
KPI's own value in another period (three earlier)	M(-3p)
KPI's own threshold in another period	T(field, -3p)
another KPI value	M(kpi_id)
another KPI threshold	T(kpi_id, field)
another KPI value in another period	M(kpi_id, -3p)
another KPI threshold in another period	T(kpi_id, field, -3p)
another scorecard item score	S(item_id)
another scorecard item score in another period	S(item_id, -3p)
initiative item's value (see Calculated KPIs for list of fields)	I(field, initiative_id)
To-date aggregation (Sum or Average)	TD(calendar, kpi_id, field, aggregation)

Text (String) Functions	Format	Notes
Empty (blank, null) check	isblank(mystring)	returns true or false
Concatenation	mystring1 + mystring2
Convert number to string	str()
Left	left(mystring, 4)	first 4 characters
Right	right(mystring, 4)	last 4 characters
Middle	mid(mystring, 2, 3)	3-character string starting at the second character
Substring	substr(mystring, 2, 3)	Starting at the second character and ending at the third
Substring	substr(mystring, 2)	Everything starting at the second character
Lower Case	lower(mystring)
Upper Case	upper(mystring)
Length	len(mystring)
Trim Whitespace	trim(mystring)
Contains	contains("something", "s")	Returns true, case sensitive
Does not Contain	!contains("something", "s")	Returns false, case sensitive

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 (ISO-8601)	date(mydate)
Date parse (example, March 15, 2020)	date(mydate, 'm d, y')

Probability & Statistics 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, …)
Random Number (between 0 and 1)	rand()
Binomial coefficients	binom(n, i)
Modulus (remainder when x is divided by y)	mod(x,y)

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)
Absolute Value / Magnitude	abs(x)
Signum (-1,0,1 depending on sign of argument)	signum(x)

Trigonometry 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)
Co-tangent	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 & Exponential Functions	Format
Natural Logarithm	ln(x)
Logarithm base 10	log(x)
Logarithm base 2	lg(x)
Exponential (e^x)	exp(x)
Power	pow(x)
Square Root	sqrt(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.