Spider Strategies Blog

The latest news about our company and software

Exploring the Data Behind KPI Calculations in 3.2

Scott O'Reilly, Nov 15, 2018

It’s now easy to see what numbers go into a calculated KPI value. Equations can be very complex, and it can sometimes be difficult to understand the nuanced differences between aggregation types like “sum” and “use equation.”

Although this explanation gets a little technical, don’t worry, the software is easier to use than ever. The only thing that has changed is that you can hover your mouse over a number to see how it was calculated.

On the Scorecards Overview tab there has always been an “Actual and Threshold Values” table that shows you all of the KPI values and thresholds that go into the currently selected KPI, regardless of whether those values are calculated or not. In version 3.2 you can now interact with these numbers to see where they come from.

In this example, we’re looking at a monthly KPI in quarterly mode. The KPI’s aggregation type is average, so its quarterly total of 1,869 was determined by averaging its three monthly values listed in the “actual and threshold values” table.

Again, nothing new, but here’s where it gets interesting. If you hover your mouse over the April 2018 monthly values, you can now see that it’s based on an equation using the values of four other KPIs.

If you want to explore further, just click the monthly number that you’re hovering over. This shows a dialog containing both the original equation and a version of the equation with April 2018 actual values substituted for the KPI references. When you hover over individual parts of the equation, there’s a tooltip telling you the name of that KPI, and its corresponding actual value is highlighted below.

Finally, you can click on any of these KPI references to go to that KPI’s Scorecard Overview tab. By doing this, you can trace down complicated nested equations to find out exactly where a calculated number comes from.

Instead, we’ll edit our original KPI to show how the software visualizes different aggregation types. We’ll change the calculated aggregation type from “average” to “use equation.”

Now when we go back to the Overview tab, things look different in the “actual and threshold values” box. The goal and red flag thresholds are manually updated for this monthly KPI, so they’re still in monthly rows. But, a single quarterly actual value is now listed on top rather than separate actual values for every month.

What’s happening is that we changed the way the KPI’s quarterly value is calculated when we changed the calculated aggregation type. The software’s layout in version 3.2 now reflects that.

As we saw earlier, this KPI’s actual value is calculated, and its calculated aggregation type is now “use equation” rather than “average.” The “average” aggregation type calculated each of this KPI’s monthly values and then averaged them. The “use equation” aggregation type does things in the opposite order. It first calculates the quarterly values for every KPI referenced in the equation, and then plugs those values into the equation.

Long story short, this KPI’s monthly values aren’t actually used to determine its quarterly value anymore, so the software doesn’t show those monthly values in the table.

Just like before, when we hover over the calculated value, we can see its equation in the tooltip.

And, like before, clicking on the calculated number shows a dialog. This time, however, the equation is using quarterly values rather than monthly. You can see that each of the KPIs in the equation have first been aggregated on their own, because their Quarter 2 values are used in the equation.