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. For a comprehensive list of all equation syntax and functions, see the Equations 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.

1. We've changed the KPI's actual value from Manual to Calculated.
2. The Goal and Red Flag thresholds are still manually updated. As you can see, we've typed in default values for both.
3. We can choose a Manual Aggregation Type like Sum or Average. This only applies to the threshold values because they are manually updated.
4. 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.
5. 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.
6. 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.

1. This is where the equation that we're building goes. You can type text directly here.
2. 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.

1. The item type is KPI. We'll explain below how to reference other things like Initiatives and Scores.
2. 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.
3. We're going to include Product Revenue's actual value in the equation, but we could reference one of its thresholds like Goal instead.
4. We're going to include Product Revenue's value for the current period, but we could choose earlier or later periods.
5. 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

The Equations article has 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.

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 KPI Values

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 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)

Blank (null) and Not Applicable (N/A)

In addition to their normal values, KPIs can also have values that are:

1. Blank (also called null).
2. 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 `isblank` and `isna` functions like this:

if(isblank(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, blank, 20)

Returning a N/A value would look like this:

if(M(123) > 8, na, 20)

Full equation syntax

For a comprehensive list of all equation syntax and functions, see the Equations article.