Skip to main content

Calculations and formulas

Behind every number in the dashboard there is a calculation: a count, a sum, an average, a percentage. The choice of calculation changes the result and, sometimes, the decision. A site's average compliance is not the same as its median; the number of collections is not the same as the number of nonconformities; and an approval rate must divide approvals by the right total.

Functions cover the most common calculations with one click. Formulas combine functions to create your own indicators: rates, share of total, variation from the previous month, stock balance, weighted scores.


Calculation functions​

When you open a metric in the chart editor, the Quick function tab lists the available functions:

FunctionOn screenCalculatesWhen to use
CountCount (Count of records)Number of recordsHow many collections, audits, interviews.
Unique countUnique countHow many different values a field hasHow many sites audited, how many active auditors.
SumSumSum of valuesTotal occurrences, items, hours, quantities.
AverageAverageSum divided by the countAverage audit result, average temperature, average score.
MedianMedianThe middle value, with half the values above and half belowTimes and values with extremes that distort the average.
Minimum and maximumMinimum / MaximumThe lowest and highest valueWorst and best result, lowest and highest temperature.
PercentilePercentileThe value below which a percentage of records falls"90% of visits take up to X minutes."
Percentile rankPercentile rankThe percentage of records below a value"What share of chambers stayed below 4 °C?"
Last valueLast valueThe value of the most recent collectionCurrent readings: tank level, hour meter, stock.
Standard deviationStandard deviationHow much values vary around the averageProcess stability (weights, temperatures).
Cumulative sumCumulative sumThe sum of previous periods plus the current oneRunning total of collections through the year.
DifferencesDifferencesThe difference from the previous periodHow much the indicator went up or down from one month to the next.
Moving averageMoving averageThe average of the last N periodsSmooth daily swings to see the trend.

Cumulative sum, differences and moving average are calculated over the horizontal axis date grouping. Use them in charts with a Date histogram.

Average or median​

The editor usually picks Median when you place a numeric field on the chart. For result indicators (scores, compliance percentages), the usual choice is the average. See the difference with five audits from one site:

ResultsAverageMedian
90, 88, 85, 84, 3075.485

The average is pulled down by the audit at 30; the median ignores it. If the official indicator is the average, use Average in every chart, so the dashboard numbers match the reports.

Count of records and count of occurrences​

Count of records counts collections (records), not "Yes" answers or occurrences.

  • To count collections with a specific answer, use Count of records with a filter (for example, status : "Rejected").
  • To count occurrences recorded as numbers (for example, options with value 1 for "Yes"), use Sum of the field.
  • In a transposed index, each collection becomes several records: use Sum of the value field.
  • In a multiple-choice question, a collection is counted once for each selected option.

Formulas​

A formula combines functions, numbers and operations. To use one:

  1. Open the metric in the chart editor.
  2. Click the Formula tab.
  3. Type the formula. The editor suggests functions and fields as you type, and shows each function's documentation in the help button.
  4. In Value format, choose the result format (for example, Percent for a rate).

Elements of a formula​

ElementExampleNote
Function on a fieldaverage(temperature)The same functions as in the table above: count, unique_count, sum, average, median, min, max, percentile, last_value, standard_deviation.
Field with a hyphenaverage('perc-total')Names with hyphens or special characters go in single quotes.
Count of recordscount()No field.
Filter in a functioncount(kql='status : "Rejected"')The kql parameter takes a search bar expression, in single quotes.
Time shiftaverage('perc-total', shift='1M')The same calculation for the previous month. Other values: 1d, 1w, 3M, 1y.
Operations+ - * / and parenthesesFollow the usual order of operations.
Math functionsround(x, 1), abs(x), clamp(x, 0, 100), pow(x, 2), sqrt(x)Round, absolute value, limit to a range, power, square root.
Overall totalsoverall_sum(count()), overall_average(...)The total or average of all chart groups, to calculate shares.
Quotes inside the filter

The kql='...' filter goes in single quotes, and the values inside it in double quotes: count(kql='approval : "Needs adjustments"'). If the value contains an apostrophe, write \' instead.

Ready-made formulas​

IndicatorFormulaFormat
Rejection ratecount(kql='status : "Rejected"') / count()Percent
Approval rate among evaluated (ignores those not evaluated)count(kql='status : "Approved"') / count(kql='status : *')Percent
Positivity (positives among those tested)count(kql='result : "Positive"') / count(kql='tested : "Yes"')Percent
Referral of positivescount(kql='referred : "Yes" and result : "Positive"') / count(kql='result : "Positive"')Percent
Share of each group in the totalcount() / overall_sum(count())Percent
Out of temperature rangecount(kql='temperature > 4 or temperature < 0') / count()Percent
First time (for example, respondents who never took part)count(kql='took-part-before : "No"') / count()Percent
Balance from an empty-space reading (capacity minus measured empty space)500 - last_value('empty-space', kql='empty-space : *')Number
Tank occupancy percentage(500 - last_value('empty-space', kql='empty-space : *')) / 500Percent
Percentage of maximum pointssum(points) / sum('possible-points')Percent
Weighted average of two scores (weight 2 and weight 1)(average('practical-score') * 2 + average('theory-score')) / 3Number
Nonconformities per auditsum('nc-count') / count()Number
Last value ignoring collections without the reading

last_value returns the value of the most recent collection. If that collection doesn't have the field filled in, the result is empty. The filter kql='field : *' makes the function consider only collections where the field is filled in.

Percentages: Percent format or % suffix

A rate calculated by division (for example, 0.25) should use the Percent format, which shows 25%. A field that already comes multiplied by 100 from the form (for example, 85) should use the Number format with the % suffix. Mixing the two produces values such as 0.85% or 8,500%.

Variation from the previous period​

IndicatorFormulaFormat
Percentage change from the previous monthaverage('perc-total') / average('perc-total', shift='1M') - 1Percent
Difference in points from the previous monthaverage('perc-total') - average('perc-total', shift='1M')Number
Collections compared with the same period last yearcount() / count(shift='1y') - 1Percent

In a card, place the current value as the primary metric and the variation as the secondary metric, with color by value (green for positive, red for negative).

Division by zero

If the previous period has no collections, the division has no result and the chart shows an empty value. This is expected. To avoid it, choose a time range in which both intervals have collections.


Static value​

The Static value tab sets a fixed number in a dimension. It is mainly used for:

  • Targets in reference lines.
  • Minimum, maximum and target of gauges.
  • Maximum of a card with a progress bar.

Advanced metric options​

Under Advanced, inside each metric:

OptionWhat it does
Filter byApplies a filter to that metric only. See Filter on a metric.
Time shiftCalculates the metric shifted in time. See Compare with the previous period.
Reduced time rangeUses only the final part of the time range (for example, the last hour), useful for "current" readings in monitoring dashboards.

When to calculate in the form​

Some rules work better in the form itself, as a calculated field, than in a dashboard formula:

  • Rules with several conditions on the same collection ("positive if A or at least two of B, C and D").
  • Classifications that must appear in the response and in the form's reports (for example, "Approved" or "Rejected").
  • Scores by tag, such as sumByTag and avgByTag.

The result reaches the index ready, and the dashboard only needs to count, sum or average it. See Calculated fields and tags.