KPI row calculations: detailed rules

This page gives the detailed rules behind the month cells and the Sum column of a KPI scorecard row: what an empty month or an empty row shows, which documents a system row leaves out, and which documents a split or a one-value filter matches. What each calculation type shows is summarised in Calculations and traffic lights in a KPI scorecard; this page adds the details and the exact rule for system rows with Average and with Average - Sum on months.

Empty months and rows with no values

Row and calculation type	What is shown
Manual row with Sum, Count or Average, with no month filled in	The Sum column of the manual row shows 0.
Manual row with Show last registration, with no month filled in	The Sum column of the manual row is empty.
System row with Count, in a month with no matching document	The month cell of the system row shows 0.
System row with Count, with no matching document in the year	The Sum column of the system row shows 0.
System row with Sum, Average or Average - Sum on months, in a month where no matching document has a value in the number field	The month cell of the system row is empty, not 0.
System row with Sum, Average or Average - Sum on months, with no matching document in the year that has a value in the number field	The Sum column of the system row is empty.
System row with Show last registration, in a month or a year with no matching document	The month cell, or the Sum column, of the system row is empty.

So a row with no values shows differently depending on its kind: a manual row with Average shows 0 in the Sum column, while a system row with Average shows an empty Sum column.

Documents a system row leaves out or includes

Rule	What it means for a system row
Every module version counts	A system row counts documents from every version of the module chosen in Choose module, so documents created before the module was last changed are included.
Empty date field	When Module date fields is one of the module’s own date fields, a document where that date field is empty is not counted in any month or in the Sum column of the system row.
Empty number field with Sum	On a system row with Sum, a document with an empty number field adds nothing to the month or the year.
Empty number field with Average	On a system row with Average, a document with an empty number field is left out: the average is taken only over the documents that have a value in the field chosen in Module fields. This applies to the month cells and to the Sum column.
Empty number field with Average - Sum on months	On a system row with Average - Sum on months, a document with an empty number field is left out of the month totals and of the Sum column.

The Sum column of Average - Sum on months

On a system row with Average - Sum on months, each month cell shows the total of the number field for that month. The Sum column shows the average of the number field over every matching document in the year that has a value in the field; that is the same figure a system row with Average shows in its Sum column. It is not the average of the month totals that have a value.

Example: a system row with Average - Sum on months counts four documents. Three are dated in January with the values 2, 4 and 6, and one in February with the value 8. The January cell shows 12 and the February cell shows 8. The Sum column shows 5 (20 divided by 4 documents), not 10.

Splits and one-value filters: which documents match

These rules apply to the sub-rows created with Drill down column in the system row dialog, and to a row made with Get data from a module, filtered by one value in a drop-down menu.

Field the row is split or filtered on	Which documents a sub-row, or the filtered row, counts
Checkbox field	Every document where that option is ticked, also when other options are ticked as well. A document with three options ticked counts once in each of the three sub-rows.
Global field	Every document where that value is among the values chosen in the field.
Connected dropdown	Every document where that option, or a value below it in the connected dropdown, was chosen.

Because of this, the sub-rows of a system row split by a checkbox field or a global field can add up to more than the number of documents.

Next step

KPI traffic lights: detailed rules