Historic comparisons in a single panel

Grafana Time Series Panel
1

I am attempting to perform some analysis within Grafana. Namely I would like to be able to compare historical data trends in call volumes. Our data roughly follows a weekly trend as call volumes are dependent on business opening hours. I want to overlay many periods of data into a single graph and find trends within that data.

As a result it is helpful to see today’s call volumes as compared with the same time frame last week, and the week before etc. To achieve this at present, I am running the same queries in 3 separate panels. The first with no time shift then a panel with 168h and one with 336h timeshifts. I can no specify more than one timeshift per panel. Lets call these PanelCurrent, PanelLastWeek and PanelTwoWeek.

For example, if my time field is 9am to 5pm on Monday. This shows the current Monday, last Monday and the Monday before on 3 separate panels.

I had planed to add a field from calculation, Mode: Row Index. Then each panel would have indexed time, which I could then be plotted on the same graph.

My issue Is that I can not find a way to pull data from multiple sources into the same panel within grafana. The data source “Dashboard” only allows me to select a single source. I also can not use the “Mixed” data source to pull data from more than one dashboard element.

Is there a way of pulling multiple panels of data into a single panel?
Alternatively, can I set timeshift on a per query basis?

Unfortunately the backend of the data is managed so I cannot change configurations. The database is clickhouse and the data source is a Loki.

2

so, the stock time series might not be possible unless you somehow skew the historical data’s timestamp and metric label?

image
image982×720 60.5 KB

using mssql, I can overlay things by bucketizing things by shift and by hour of day, that way things line up nice and clean.

image
image1076×358 2.35 KB

1 Like
3

Hi Yosiasz,

I am using the Loki data source. This links to a clickhouse database.

Thanks,
Andrew

4

I was attempting to take a similar approach. Unfortunately the data source I am using does not allow full SQL code to be sent, only an intepreter/templating engine somewhat like YML. I also do not have direct access to the database or to configure the data source hence am trying to do everything through grafana’s front end.

At present I have used the Row index to bucketize the data on a single panel. If I could join multiple panels’ data into a single panel. This would give me consistent buckets, provided the interval remains the same on all panels.

Is there a way to take the data from multiple panels and feed that into a single panel?

Otherwise If there is a way to set Query options on a per query basis, rather than for all queries to a data source. (Query options appear but are different from the data source query options)

image
image2131×908 67.1 KB

I’ve added a screenshot to illustrate what I mean.

5

Update, I have actually found a plugin for grafana that appears to do exactly what I’m looking for:

This seems to open up the option of different Time shifts for the same query into a single Panel. Provided the overhead isn’t too bad this should work.

1 Like
6

so what I showed you was how you could approach it as a principle. not to use sql code.

7

Ah OK. I think I was on a similar track with Row index as that will bucketize as long as intervals are the same, although it would require re-setting the labels.

Thank you for your help.

1 Like