Calculate difference in current value and previous value

Grafana InfluxDB
, ,
1

So, I have a dataset which has time stamp column and the cumulative value column, what I want to do is calculate the difference of the current cumulative value from the previous one so I can get the actual value and the difference of all those values need to be summed up and get a single value, example C2=B3-B2, C3=B4-B3 and so on and at last C2+C3+C4 and so on. I have attached my dataset sample below.

image
image582×553 8.49 KB

My query is as follows:

from(bucket: "Bacnet_Network")
  |> range(start: 2022-01-01T00:00:00Z, stop: 2022-12-31T23:59:59Z)
  |> filter(fn: (r) => r["_measurement"] == "048_STEM")
  |> filter(fn: (r) => r["sensor"] == "472200_AV_210_STEM_HRC2_GJ_EXPORT_AV")
  |> difference(nonNegative: false, columns: ["_value"])
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false)
  |> group(columns: ["sensor"])
  |> sum(column: "_value")
  |> drop(columns: ["host", "system", "unit", "topic", "objects_type", "name", "_measurement"])

The answer that I am getting is not correct and does not match with the raw data. Any suggestions?

THANK YOU!!

2

Hi @asingh25

I do not have time at the moment to test this out on my data, but something like this might work:

from(bucket: "Bacnet_Network")
  |> range(start: 2022-01-01T00:00:00Z, stop: 2022-12-31T23:59:59Z)
  |> filter(fn: (r) => r["_measurement"] == "048_STEM")
  |> filter(fn: (r) => r["sensor"] == "472200_AV_210_STEM_HRC2_GJ_EXPORT_AV")
  |> group(columns: ["_time"], mode: "by")  // not sure if this line is needed or not
  |> difference(nonNegative: false, columns: ["_value"])
  |> sum(column: "_value")
  |> drop(columns: ["host", "system", "unit", "topic", "objects_type", "name", "_measurement"])
  |> yield(name: "difference")