How to do a basic summation?

joshuaeblackburn · June 30, 2023, 3:59pm

What Grafana version and what operating system are you using?
Running a Docker container from the grafana/grafana image, launched 6/28/23 so likely the most recent version.
What are you trying to achieve?
I’m trying to sum several metrics into one total number. I have 5 power meters reporting their total energy usage for the month and I would like to sum those into one total power usage number.
How are you trying to achieve it?
I can grab the usage numbers individually using the following query and a stat visualization, filtered by last* value:

from(bucket: "energy")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "s-31" and
    r._field == "totalwh"
  )

I would like a single stat that contains the sum of all 5 numbers.
I have tried using a math expression with Sum($A) (or sum or SUM), but it simply says ‘no data’.
I have tried using an ‘add field from calculation’ transform with reduce row, total, and selecting the 5 power usage fields, but it just displays the value of one of them.

What happened?
Described above.
What did you expect to happen?
I would have expected either method to provide a summation of the last values of all 5 fields, or at least some form of a sum of something.
Can you copy/paste the configuration(s) that you are having problems with?
The query is above, as is the math expression, I don’t know how to copy paste the transform, since it’s a gui configuration.
Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
The transform gives no errors, just nonsensical results. The math expression gives ‘no data’ with this error:

Query data error
Object
status:500
statusText:""
data:Object
message:"Query data error"
traceID:""
config:Object
url:"api/ds/query?ds_type=__expr__&expression=true&requestId=Q279"
method:"POST"
data:Object
requestId:"Q279"
hideFromInspector:false
headers:Object
retry:0
traceId:undefined
message:"Query error: 500 "

Did you follow any online instructions? If so, what is the URL?
No, just general googling around and experimenting with the interface.

If there’s anything else I can add please let me know, I’m very new to grafana.

Edit: Basically I have this setup:

and what I’m looking for is a single stat that is the sum of all 5 of those values (2.05+5.4+1.72+0.165+0.863 = 10.198)

grant2 · June 30, 2023, 4:08pm

Welcome @joshuaeblackburn to the Grafana forum.

My guess is that the last value of each of the 5 series all have different timestamps, and that is causing the Transformation to not work.

Maybe try using two Transformations, like this?

from(bucket: "energy")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "s-31" and
    r._field == "totalwh"
  )

Does the above query just return a time series of data? For the other 4 numbers, does only the measurement change (e.g. “s-32”)?

joshuaeblackburn · June 30, 2023, 5:14pm

They do indeed have different timestamps, the 5 power meters report on their own time, every 5 minutes.
Using two transforms doesn’t seem to change anything, the Total field is still just one of the measurements, and the Last * field seems to just be the same as the Total field.

The query

from(bucket: "energy")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "s-31" and
    r._field == "totalwh"
  )

gives this result

The measurements are differentiated by the r.topic filter, which I’m not using since I want every topic. So if I just wanted one of the measurements I would add the line r.topic == "tele/server-s31/SENSOR" and to the filter section.

grant2 · June 30, 2023, 5:51pm

OK, how about using the same query…

from(bucket: "energy")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "s-31" and
    r._field == "totalwh"
  )

without any transformations and then using these options on the right side of Grafana?

joshuaeblackburn · June 30, 2023, 6:10pm

I do not appear to have an option for Last * in the Fields section.

scrolling down the list just shows the Time and totalwh options for the other two power meters.

grant2 · June 30, 2023, 6:13pm

What happens if select All Fields? Does it give the right number in the Stat panel?

or if you select Numeric Fields? Does that give the right number?

joshuaeblackburn · June 30, 2023, 6:21pm

Numeric Fields is what I’ve been on this whole time, along with Last * as the reducer function.

Switching to All Fields simply adds Time to the visualization:

I’ve noticed the value in the Total section always matches the most recent data point from any of the 5 meters, so when one reports, the Total field updates to reflect that latest measurement.

Edit: forgot to mention, selecting Total in the reducer function simply adds up all the reported values, so I end up with numbers for each meter that are gigantic. Since they report every 5 minutes, the server-s31 stat, for example, simply adds 5.4 to its total every 5 minutes, resulting in a huge number.

joshuaeblackburn · June 30, 2023, 6:41pm

Switching to table view shows what I assume is the issue:

It appears that the Total transform only considers data points with the exact same timestamp, and since the meters report asynchronously, it grabs the latest timestamp and assumes the other data points at that time are zero. So it seems that I need to tell it to total the last data point of each series, rather than the data points at the latest timestamp. How to do that, I have no idea…

grant2 · June 30, 2023, 6:59pm

Start here: date.truncate() function | Flux Documentation

joshuaeblackburn · June 30, 2023, 7:13pm

Ok, so I now have the ability to grab only the data from the last 5 minutes, which leaves me with a single value for each meter, instead of a series of data points. They still have asynchronous timestamps though and so the problem remains. I assume what you’re suggesting is to round each timestamp to the nearest 5 minutes so they all match, but I’m not seeing how to do that.

yosiasz · June 30, 2023, 7:39pm

does the last data point of each series have the exact time stamp? otherwise back to square one

Please post sample data from your bucket in csv format

bucket,measurement,_field,_value,timestamp
energy,dfd,s-31,totalwh,33,293829839283

joshuaeblackburn · June 30, 2023, 8:16pm

No, the timestamps of the last data points for each power meter are within 5 minutes of each other, but they are not the same, which is exactly what’s causing the issue. However, if I could round the timestamps in grafana to the nearest 5 minutes, then they would match.

Here’s a section of the csv data:

,result,table,_start,_stop,_time,_value,_field,_measurement,host,topic
,,0,2023-06-30T19:10:54.965976852Z,2023-06-30T20:10:54.965976852Z,2023-06-30T19:13:10Z,2.218,totalwh,s-31,telegraf,tele/growlight-s31/SENSOR
,,1,2023-06-30T19:10:54.965976852Z,2023-06-30T20:10:54.965976852Z,2023-06-30T19:12:50Z,5.646,totalwh,s-31,telegraf,tele/server-s31/SENSOR
,,2,2023-06-30T19:10:54.965976852Z,2023-06-30T20:10:54.965976852Z,2023-06-30T19:13:20Z,1.796,totalwh,s-31,telegraf,tele/switch-s31/SENSOR
,,3,2023-06-30T19:10:54.965976852Z,2023-06-30T20:10:54.965976852Z,2023-06-30T19:14:20Z,0.169,totalwh,s-31,telegraf,tele/v0-s31/SENSOR
,,4,2023-06-30T19:10:54.965976852Z,2023-06-30T20:10:54.965976852Z,2023-06-30T19:12:50Z,0.866,totalwh,s-31,telegraf,tele/workstation-s31/SENSOR

As you can see, the timestamps are at 19:13/12/13/14/12, all within five minutes, but not synchronized at all.

yosiasz · June 30, 2023, 8:22pm

there is a way to strip off the timestamp and get it “synched”. not sure if that will help. let me find the method unless @grant2 remembers

joshuaeblackburn · July 1, 2023, 2:46pm

I figured out a messy way to do it.
First create a separate query for each data series:

from(bucket: "energy")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "s-31" and
    r.topic == "tele/growlight-s31/SENSOR" and
    r._field == "totalwh"
  )

This by itself yields a single data series, like this:

Then create a reduction expression for each data series query to grab only the last value:

Then finally create a math expression and add all the reduction expression values together:

I say this is messy because for n number of data series being summed, you need 2n+1 queries/expressions, so the query page gets real cluttered. There’s also the drawback that, while it does return the sum of the latest value in each data series:

it returns exactly one value, so the usual trendline in the stat visualization is missing.

If anyone knows a better way to do this, please let me know, but I’m marking this as the solution for now.