Overview
You can set up KPIs to automatically calculate their actual values and thresholds from other values in Spider Impact. For more information about what Spider Impact actually does when it's calculating a KPI field, please see the Exploring How a KPI is Calculated article.
Calculated KPI Details
Let's start things off with a simple example. We're going to create a calculated KPI called Total Revenue that is the sum of Product Revenue, Training Revenue, and Book Revenue.
There's a lot going on in the Edit tab, so we'll look at each configuration option one at a time.
- We've changed the KPI's actual value from Manual to Calculated.
- The Goal and Red Flag thresholds are still manually updated. As you can see, we've typed in default values for both.
- We can choose a Manual Aggregation Type like Sum or Average. This only applies to the threshold values because they are manually updated.
- We can choose a Calculated Aggregation Type. The options here are Sum (recommended) and Use Equation. This only applies to the Actual Value because it is calculated.
- By default, if a KPI equation is referencing a value that is blank, the entire equation will immediately evaluate to blank. You can instead choose to treat missing values in the equation as 0, N/A, or Blank.
- This is the button to set the KPI's equation. We'll cover that next.
KPI Equations
When you click the Set Equation button, it shows a dialog for building the equation for that series.
- This is where the equation that we're building goes. You can type text directly here.
- This builds functions that we send into the equation.
Next we're going to choose the KPI that we want to reference by clicking the Select a KPI button.
This stacks a second-level dialog where we choose the Product Revenue KPI.
When we click Done and the second-level dialog closes, our equation builder now looks like this.
- The item type is KPI. We'll explain below how to reference other things like Initiatives and Scores.
- This is the Product Revenue KPI that we selected. If you want to change the item we're going to reference in the equation, just click this button again.
- We're going to include Product Revenue's actual value in the equation, but we could reference one of its thresholds like Goal instead.
- We're going to include Product Revenue's value for the current period, but we could choose earlier or later periods.
- When we're ready to add the data to the equation, click this Add button.
Once you add a reference to a KPI, it looks like this:
As you can see, the syntax for referencing another KPI value is:
M(kpi_id)
Now we'll type in a [space], a [+], and a [space], select the Training Revenue KPI in the second-level dialog...
... and click Add.
One more time and our equation is finished. Let's click Done.
Back on the Edit tab, our equation now shows up for the Actual Value series.
When we save the KPI and visit the Scorecards Overview tab, we can now see our calculated KPI in action.
Common Equation Syntax
Later in this article is a comprehensive list of all functions and operators that you can use in your equations, but here are examples of some of the most commonly used syntax.
Most Common Formulas |
Format |
Sum |
sum(x, y, …) |
Average |
avg(x1, x2, x3, …) |
If |
if(condition, truevalue, falsevalue) |
Round |
round(x), round(x, decimal_places) |
To Date (YTD, QTD, etc.) |
TD(calendar, kpi_id, series, aggregation) |
Referencing a KPI (metric) value |
M(kpi_id) |
Referencing an Initiative Value |
I(field, initiative_id) |
If you select a block of text in your equation, a tooltip dialog will appear allowing you to wrap the selected text in a function.
Self-Referential Equations
You can also choose This KPI when building equations. This allows you to make self-referential equations, for example goals that are automatically 10% higher than the previous year’s actual value.
To reference a KPI's own value, the syntax is simple:
M()
If you want to reference a KPI's own threshold (Red Flag), it looks like this:
T(Red Flag)
A KPI's own value for three periods earlier looks like this:
M(-3p)
Similarly, a KPI's own threshold (Goal) from three periods earlier looks like this:
T(Goal, -3p)
Referencing Scores
You can include the score from any scorecard item in your equation. Just select Score from the Type dropdown on the left.
This is the syntax for referencing the score for the current period for a scorecard item with an ID of 123 is:
S(123)
The syntax for referencing a scorecard item's score in another period is similar. In this example, we're referencing the score from the previous period for item 123.
S(123, -1p)
Referencing Initiative Values
You can include vales from Initiative items by selecting Initiative in the Type dropdown on the left. Here we're using the Total Budget from the Migrate Servers to the Cloud initiative in our equation.
They syntax when referencing an initiative value is:
I(field, initiative_id)
For example, to reference the budget remaining for initiative 123, the equation would be:
I(BR, 123)
You can reference values for these initiative fields:
- BR – Budget Remaining
- DE – Days Elapsed
- MSTD – Money Spent to Date
- PBV – Projected Budget Variance
- PBVP – Projected Budget Variance Percentage
- PC – % Complete
- PCOST – Projected Cost
- PSV – Projected Schedule Variance
- PTE – % Time Elapsed
- TB – Total Budget
To-Date Function
By choosing To Date in the Type dropdown, you can build equations that aggregate values over time. The most popular use of the to-date function is calculating Year To Date values. In this example we're including the Sales Revenue quarterly sum.
The syntax for the to-date function is
TD(calendar, kpi_id, field, aggregation)
For example, if we wanted to do an average year-to-date for actual value of the KPI with an ID of 123, the equation would be:
TD(Yearly, 123, Actual Value, Average)
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))
Yes/No KPIs
Most KPIs have number for values, but Yes/No KPIs are different. These can be referenced as booleans (true/false) or as numbers (1/0).
In this example, we're referencing a Yes/No KPI's value in a number KPI's equation. "If the value for KPI #123 is yes, return 5. Else return 20"
if(M(123), 5, 20)
In this example, we're referencing a number KPI's value in a Yes/No KPI's 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.
Blank (null) and Not Applicable (N/A)
In addition to their normal values, KPIs can also have values that are:
- Blank (also called null).
- Not Applicable (also called N/A). This is only available when the "Show N/A Option" is enabled in Application Administration.
You can reference N/A and empty values using the `isnull` and `isna` functions like this:
if(isnull(M(123)), 5, 20)
and this:
if(isna(M(123)), 5, 20)
To set a value to empty, the equation would look like this:
if(M(123) > 8, null, 20)
Returning a N/A value would look like this:
if(M(123) > 8, na, 20)
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 above for more information and examples about using the most popular functions.
Spider Impact Functions |
Format |
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 above for list of fields) |
I(field, initiative_id) |
To-date aggregation (Sum or Average) |
TD(calendar, kpi_id, field, aggregation) |
Empty (null) value check |
isnull(kpi_id) |
N/A (not applicable) value check |
isna(kpi_id) |
Statistical Functions |
Format |
Average |
avg(x1, x2, x3, …) |
Sum |
sum(x1, x2, x3, …) |
Minimum |
min(x1, x2, x3, …) |
Maximum |
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) |
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 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) |