How to select an aggregated value for a stat panel

Grafana InfluxDB
1

  • What Grafana version and what operating system are you using?
    Docker Container with Grafana v9.2.0 + InfluxDB v 1.8.9

  • What are you trying to achieve?
    I have a double aggregation on a measurement: First determine the max value of a day, secondly I sum up all daily values for a month. This monthly value should be displayed in a stat panel.
    I do this with a query written in flux and it works. However, my result consists of multiple values which are shown in the stat panel but I only want to display the final sum value

  • How are you trying to achieve it?
    This is my flux statement so far:

from(bucket: "iobroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => (r._measurement == "pv_tagesertrag") and r._field == "value")
  |> aggregateWindow(every: 24h, fn: max, timeSrc: "_start")
  |> yield(name:"intermediateResult")
  |> sum()
  |> set(key: "_field", value: "Hausdach")

Which results in this stat panel:

image
image1730×486 33.5 KB

The left value is the correct one which I want to keep.
I only managed to keep the right value which is not what I want to, by addig

|> keep(columns: ["sum"])

at the end:

image
image2002×498 19.7 KB

Is there a way to use e.g. an index for the keep function to tell it to only keep the left value? Or does anybody have a totally different approach which would work in this case?

2

@percy2live What happens if you replace

|> keep(columns: ["sum"])

with this?

|> drop(columns: ["sum"])

Also, can you share the query output in table form from your Influx Data Explorer? Often it is easier to poke around there and get it just right before pasting it into Grafana.

3

Hi Grant,
thanks for your reply. Unfortunately replacing keep with drop does lead to the same result as visible in the second picture.

This is the output of the data explorer (note that the values are different from the values in Grafana as the time range in Grafana was set to October and in the data explorer to the last 30 days)

image
image1254×1066 216 KB

Sorry for posting a picture but the csv export looks even more strange.
The very last value seems to be the correct sum.

Many thanks for your assistance!

EDIT: I used an html converter for the csv, this is the result:

HTML Table

#group false false true true true true false FIELD9
#datatype string long dateTime:RFC3339 dateTime:RFC3339 string string double
#default _result
result table _start _stop _field _measurement _value
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z Hausdach pv_tagesertrag 270
#group false false true true false false true true
#datatype string long dateTime:RFC3339 dateTime:RFC3339 dateTime:RFC3339 double string string
#default intermediateResult
result table _start _stop _time _value _field _measurement
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-16T19:23:17.450403421Z 7 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-17T00:00:00Z 17 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-18T00:00:00Z 8 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-19T00:00:00Z 6 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-20T00:00:00Z 7 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-21T00:00:00Z 5 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-22T00:00:00Z 15 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-23T00:00:00Z 9 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-24T00:00:00Z 12 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-25T00:00:00Z 9 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-26T00:00:00Z 9 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-27T00:00:00Z 14 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-28T00:00:00Z 11 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-29T00:00:00Z 9 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-30T00:00:00Z 13 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-10-31T00:00:00Z 9 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-01T00:00:00Z 8 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-02T00:00:00Z 15 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-03T00:00:00Z 7 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-04T00:00:00Z 5 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-05T00:00:00Z 9 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-06T00:00:00Z 7 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-07T00:00:00Z 6 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-08T00:00:00Z 11 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-09T00:00:00Z 3 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-10T00:00:00Z 13 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-11T00:00:00Z 3 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-12T00:00:00Z 2 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-13T00:00:00Z 13 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-14T00:00:00Z 4 value pv_tagesertrag
0 2022-10-16T19:23:17.450403421Z 2022-11-15T19:23:17.450403421Z 2022-11-15T00:00:00Z 4 value pv_tagesertrag
4

HI @percy2live

OK, going back to the query where you got the two results (but want only the left one), try doing a transformation like this:

BEFORE:

image
image1306×832 71.7 KB

AFTER:

image
image1306×1037 79.5 KB

1 Like
5

Thanks Grant, that did the trick.
I also found a way to alter my flux query so it only returns a single value. This might be an alternative for someone looking into this thread:

from(bucket: "iobroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => (r._measurement == "pv_gartenhaus_energy_daily") and r._field == "value")
  |> aggregateWindow(every: 24h, fn: max, timeSrc: "_start")
  |> sum()
  |> set(key: "_field", value: "Gartenhaus")
1 Like