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:
| Function | On screen | Calculates | When to use |
|---|---|---|---|
| Count | Count (Count of records) | Number of records | How many collections, audits, interviews. |
| Unique count | Unique count | How many different values a field has | How many sites audited, how many active auditors. |
| Sum | Sum | Sum of values | Total occurrences, items, hours, quantities. |
| Average | Average | Sum divided by the count | Average audit result, average temperature, average score. |
| Median | Median | The middle value, with half the values above and half below | Times and values with extremes that distort the average. |
| Minimum and maximum | Minimum / Maximum | The lowest and highest value | Worst and best result, lowest and highest temperature. |
| Percentile | Percentile | The value below which a percentage of records falls | "90% of visits take up to X minutes." |
| Percentile rank | Percentile rank | The percentage of records below a value | "What share of chambers stayed below 4 °C?" |
| Last value | Last value | The value of the most recent collection | Current readings: tank level, hour meter, stock. |
| Standard deviation | Standard deviation | How much values vary around the average | Process stability (weights, temperatures). |
| Cumulative sum | Cumulative sum | The sum of previous periods plus the current one | Running total of collections through the year. |
| Differences | Differences | The difference from the previous period | How much the indicator went up or down from one month to the next. |
| Moving average | Moving average | The average of the last N periods | Smooth 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:
| Results | Average | Median |
|---|---|---|
| 90, 88, 85, 84, 30 | 75.4 | 85 |
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:
- Open the metric in the chart editor.
- Click the Formula tab.
- Type the formula. The editor suggests functions and fields as you type, and shows each function's documentation in the help button.
- In Value format, choose the result format (for example, Percent for a rate).
Elements of a formula
| Element | Example | Note |
|---|---|---|
| Function on a field | average(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 hyphen | average('perc-total') | Names with hyphens or special characters go in single quotes. |
| Count of records | count() | No field. |
| Filter in a function | count(kql='status : "Rejected"') | The kql parameter takes a search bar expression, in single quotes. |
| Time shift | average('perc-total', shift='1M') | The same calculation for the previous month. Other values: 1d, 1w, 3M, 1y. |
| Operations | + - * / and parentheses | Follow the usual order of operations. |
| Math functions | round(x, 1), abs(x), clamp(x, 0, 100), pow(x, 2), sqrt(x) | Round, absolute value, limit to a range, power, square root. |
| Overall totals | overall_sum(count()), overall_average(...) | The total or average of all chart groups, to calculate shares. |
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
| Indicator | Formula | Format |
|---|---|---|
| Rejection rate | count(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 positives | count(kql='referred : "Yes" and result : "Positive"') / count(kql='result : "Positive"') | Percent |
| Share of each group in the total | count() / overall_sum(count()) | Percent |
| Out of temperature range | count(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 : *')) / 500 | Percent |
| Percentage of maximum points | sum(points) / sum('possible-points') | Percent |
| Weighted average of two scores (weight 2 and weight 1) | (average('practical-score') * 2 + average('theory-score')) / 3 | Number |
| Nonconformities per audit | sum('nc-count') / count() | Number |
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.
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
| Indicator | Formula | Format |
|---|---|---|
| Percentage change from the previous month | average('perc-total') / average('perc-total', shift='1M') - 1 | Percent |
| Difference in points from the previous month | average('perc-total') - average('perc-total', shift='1M') | Number |
| Collections compared with the same period last year | count() / count(shift='1y') - 1 | Percent |
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).
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:
| Option | What it does |
|---|---|
| Filter by | Applies a filter to that metric only. See Filter on a metric. |
| Time shift | Calculates the metric shifted in time. See Compare with the previous period. |
| Reduced time range | Uses 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
sumByTagandavgByTag.
The result reaches the index ready, and the dashboard only needs to count, sum or average it. See Calculated fields and tags.