Mathematical Expression

Mathematical Expressions with CompareQueries

This guide explains how to build dependent calculated graphs using Grafana Math expressions together with CompareQueries time-shifted series.

It addresses Issue #19 — Add compatibility with expressions and related scenarios in Issue #12 (diff / ratio between current and shifted data).

Version requirement: CompareQueries v2.1.0+ (backend plugin enabled).

Why extra setup is needed

Grafana Math expressions run on the server. Every query referenced by an expression ($A, $B, …) must execute in backend mode.

Before v2.1.0, CompareQueries was frontend-only and could not join expression pipelines. Since v2.1.0, the plugin proxies shifted queries through Grafana /api/ds/query. That proxy needs a Service Account token configured on the CompareQueries datasource.

Flow:

Service Account Token → CompareQueries datasource (Basic Auth) → Backend plugin (/api/ds/query proxy) → -- Mixed -- panel ├─ Query A: target datasource (base series) ├─ Query B: CompareQueries (shifted series) └─ Query C: Math Expression ($A - $B) → Calculated graph

Step 1 — Create a Grafana Service Account

1. Open Administration → Users and access → Service accounts.
2. Create a service account (or reuse one) with permission to query datasources.
3. Add a Service account token with at least Viewer-level data access.
4. Copy the token for Step 2.

Step 2 — Configure the CompareQueries datasource

1. Go to Connections → Data sources → CompareQueries.
2. Set Authentication to Basic authentication.
3. Paste the Service Account token from Step 1.
4. Set Grafana URL only if auto-detection is wrong (reverse proxy, sub-path, etc.).
5. Click Save & test.

For plain dashboard viewing, No Authentication may work in some setups. For Math expressions and Alerting, use Basic authentication.

Step 3 — Build a Mixed panel

1. Edit a panel and set datasource to -- Mixed --.
2. Add a target datasource query (refId A) for the current time window — your base series.
3. Add a CompareQueries row (refId B):
   • Pick the same Target Datasource.
   • Build the same query in the embedded editor.
   • Add Time-shift rows: 1d, 1w, etc.
   • Enable Process TimeShift when you need diff/ratio on the same time axis (recommended).
   • Set Alias (suffix / prefix / absolute) so series names stay distinct.

Example layout

refId	Datasource	Role
A	Prometheus	CPU usage — today
B	CompareQueries	Same query, shift 1d, alias 1d
C	Expression	Math: $A - $B

Step 4 — Add a Math expression

1. In the same Mixed panel, Add query → Expression.
2. Choose Math (or Reduce / other types as needed).
3. Reference upstream refIds, for example:
   • Difference: $A - $B
   • Ratio: $A / $B
   • Percent change: 100 * ($A - $B) / $B
4. Run the query.

SQL / MySQL wide-series inputs

SQL datasources may return wide tables (one column per metric). Math expressions work best when each series is a numeric field with a stable name.

Tips (v2.1.1+):

1. Prefer Time series format over Table when the datasource supports it.
2. Enable Process TimeShift before subtracting or dividing.
3. Use distinct Alias per time-shift row.
4. If the expression picker misses a field, check Query inspector for separate numeric fields per shift.

Backend processing (troubleshooting)

For each Time-shift row, the backend:

1. Shifts the query time range (1d = one day back).
2. Proxies the query via Grafana /api/ds/query.
3. Filters points to the intended window.
4. Applies alias rules to field names.
5. Adds a timeshift label on each field.
6. Optionally realigns timestamps when Process TimeShift is on.

Troubleshooting

Symptom	Likely cause	Fix
Expression disabled / "frontend only"	Not on backend mode	Upgrade to v2.1.0+; enable Basic Auth
401 in backend logs	Missing or bad token	Set Service Account token
Expression cannot find $B	Wrong refId or empty shift	Check refId, shift format (1d, 1w), target returns data
Diff flat or misaligned	Timestamps not aligned	Enable Process TimeShift
SQL diff wrong columns	Wide table format	Use time-series format; distinct aliases
UI works, Alert fails	Alert always uses backend	Configure Basic Auth (see README Alerting section)

Related links

• Grafana expression queries (official docs)
• CompareQueries README
• Developer guide — backend debugging
• Issue #19