Days since last holiday gauge

Creating a “Days since last holiday” gauge #

It can be easy to forget to take holiday and end up forced to use up all your holiday days at the of the year.

Never again! With a Grafana gauge powered by QueryCal you can easily see if you’re overdue a day off.

Gauge displaying 21 days since last holiday

Pre-requisites #

  • You track your days off in your your calendar (your company’s HR software might already do this for you!)
  • A QueryCal account with your calendar added

Calculating the metric #

This recipe is going to use Grafana as an example to render the “Days since last holiday” metric as a gauge.

Grafana expects two values:

  • time which can be the current time as we’re calculating the live value of the metric.
  • value which in this case is going to be the result of counting how many days since the last holiday event.

We can calculate these values using this query:

1
2
3
4
5
6
7
SELECT DATETIME('now')                            AS time, -- returns the current time
       ROUND(JULIANDAY('now') - JULIANDAY(start)) AS value -- calculates the number of days between now and the most recent holiday event
FROM   events
WHERE  start < DATE('now') -- We only want to consider events from the past
       AND summary = "Holiday" -- 👈 Put your own condition here that matches your holiday events
ORDER  BY start DESC
LIMIT  1;  -- We only want to consider the most recent holiday event 

To customise this for your own calendar, you only need to change line 5 so that it matches your own holiday events.

Adding the gauge to Grafana #

To add the gauge to Grafana:

  1. Go to the dashboard you want to add the gauge to.
  2. Click the “Add panel” icon.
  3. Click “Add new panel”.
  4. Choose “Gauge” as the visualisation.
  5. Choose QueryCal as your data source and paste your SQL query.

Screenshot of the &ldquo;new panel&rdquo; page of a Grafana dashboard