Dynamic moving average with data from from the first point

Grafana InfluxDB
1

Hello,

I have the following problem and I would be thankfull whoever can help:

  • What Grafana version and what operating system are you using?
    I’m using Grafana v9.3.2 with InfluxDB and windows 10.

  • What are you trying to achieve?
    I want to plot the a moving average with values for the whole time range. I also want, that the plot works if I’m changing the time range.

  • How are you trying to achieve it?
    I use the moving_average() function and define the time range where to calculate manually like with “WHERE time >= 1692854036196ms and time <= 1693947043036ms” in the raw query mode. If I do not define the time range manually, the function will take the time range set in the dashboard. However, to calculate the first values for the moving average I also need the values before the despicted time range.

  • What happened?
    It plots the moving_average as aspected. However, if I change the time range I would always need to manually adjust the time range for the calculation of the moving_average manually as explained above.

  • What did you expect to happen?
    I would like to have an expression for the query which calculates the necessary time range where to calculate the moving_average, when I change the time range in the dashboard. So I would like to calculate with the variabel ${__from} (Start of the time range set in the dashboard) in the query.

  • Can you copy/paste the configuration(s) that you are having problems with?

  1. So I would like to do somthing like this:
    SELECT moving_average(mean(“X”), 360) AS “X 6hours” FROM “DataExchange” WHERE time >= (${__from}-21600000)ms and time <= ${__to}ms GROUP BY time(1m) fill(null)

  2. I also tried:
    SELECT moving_average(mean(“X”), 360) AS “X 6hours” FROM “DataExchange” WHERE time >= (${__from.value}-21600000)ms and time <= ${__to}ms GROUP BY time(1m) fill(null)

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    If I enter the first expression above, I get the error: “No data”.
    If I enter the second expression above, it simply ignores the subtraction.

  • Did you follow any online instructions? If so, what is the URL?
    I didn’t find any.

2

Welcome @stiosoli to the Grafana forum.

The above is possible, but I only know how to do it using Flux (not InfluxQL). Are you willing to perhaps set up a test system that you could configure with Flux and try it out there? Not sure how many other queries you have built over the past year (and thus how complicated converting everything would be).

Here is how one calculates the number of days selected (it could be hours or seconds or whatever too):

1 Like
3

Thank you very much. I didn’t know Flux yet. It worked with Flux.