FLUX difference() function with NULL values

Grafana InfluxDB
1

OK next problem.

import "timezone"
option location = timezone.location(name: "Europe/Warsaw")
from(bucket: "openhab_db")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "Licznik_Energa_Produkcja")
  |> filter(fn: (r) => r._field == "value")
  |> aggregateWindow(every: 1mo, fn: mean, createEmpty: false)
  |> timeShift(duration: -24h1s)
  |> difference()
  |> truncateTimeColumn(unit: 1mo)
  |> rename(columns: {_value: "Energa produkcja"})
  |> keep(columns: ["_time", "Energa produkcja"])
  |> sort(columns: ["_time"])

I want to calculate difference between start of month and now, but when I have non values on start of month then it doesn’t give any result.

I tired parameters from this page but maybe I’m making something wrong?
https://docs.influxdata.com/flux/v0.x…

2

Hi @matulekpl

Some questions:

  1. Are you sure that difference() is the right function for this? I presume you are recording data every minute, hour, etc. so the difference() function is not going to calculate the difference between the first and last unless we define the first and last points of the month (which we can do----see the end of this reply). I would think spread() would work, so long as your readings are always rising over the month, correct? Maybe you can see if it works, even when you do not have a value on the start of the month?
  2. Do you only want to see the current month? If yes, then you can use Query data from this calendar month like this:
import “date”

month = date.truncate(t: now(), unit: 1mo)

from(bucket: "openhab_db")
    |> range(start: month)

I believe you would also remove the aggregateWindow function and truncateTimeColumn function. But honestly, I am not really sure unless you try it. I guess the query would look like this:

import “date”
import "timezone"
option location = timezone.location(name: "Europe/Warsaw")

month = date.truncate(t: now(), unit: 1mo)

from(bucket: "openhab_db")
    |> range(start: month)
    |> filter(fn: (r) => r._measurement == "Licznik_Energa_Produkcja")
    |> filter(fn: (r) => r._field == "value")
    |> timeShift(duration: -24h1s)
    |> spread()
    |> rename(columns: {_value: "Energa produkcja"})
    |> keep(columns: ["_time", "Energa produkcja"])
    |> sort(columns: ["_time"])

and then maybe a yield() function. If the above does not work (even with some changes here and there), my next idea was to do something like this:

First = from(bucket: "openhab_db")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Licznik_Energa_Produkcja")
  |> filter(fn: (r) => r["_field"] == "value")
  |> first()
  |> yield(name: "First")

Last = from(bucket: "openhab_db")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Licznik_Energa_Produkcja")
  |> filter(fn: (r) => r["_field"] == "value")
  |> last()
  |> yield(name: "Last")

union(tables: [First, Last])
  |> difference()