I have this query for a bar chart
from(bucket: "Energy") |> range(start: -1w, stop: now()) |> filter(fn: (r) => r["_measurement"] == "quadro_geral") |> filter(fn: (r) => r["_field"] == "0_total" or r["_field"] == "1_total") |> aggregateWindow(every: 1d, fn: last, createEmpty: false) |> difference( columns: ["Totalgeral"], keepFirst: true)
The intention is to create a bar chart with daily usage of energy. So far, the results is the total usage accumulated since the metering was installed for each day. It doesn’t matter if I have or not the last line of the query. Seems that the difference() is being ignored.
I heard that the difference() can only work with tables, so I need to group it. If I tried to group it by day using the line below it gives an error saying: invalid: error @8:39-8:40: undefined identifier r error @8:6-8:60: expected string but found int (argument columns)
|> group(columns: [date.monthDay(t: r._time)], mode:"by")
This line would go before the difference() line.
Not sure how to accomplish what I need
Hello @Andre_Martins,
All functions work on tables. What do you mean by
I heard that the difference() can only work with tables, so I need to group it.
?
You might want to use spread instead?
If you’re always accumulating data that should work.
Can you share the output/screenshot of your data before the difference? (raw data view please)?
The reason you’re getting that error is because the group() function groups on column names and expects a string. You could convert the output of date.monthDay(t: r._time) with string(v:date.monthDay(t: r._time)). However,
the date.monthDay function returns the numerical date of the day. You don’t have any columns named 22 for example (for today).
What you can do is create a new column with the map() function and then group on that column like:
|> map(fn: (r) => ({ days: date.monthDay(t: r._time)] }))
|> group(columns: ["days"]
I’m also looking into a way to do this with a custom function passed into the aggregatewindow predicate fn parameter.
I made it work. Is almost perfect. This is what I got
`
import “date”
month = date.truncate(t: now(), unit: 1mo)
from(bucket: “Energy”)
|> range(start: month , stop: now())
|> filter(fn: (r) => r["_measurement"] == “quadro_geral”)
|> filter(fn: (r) => r["_field"] == “0_total” or r["_field"] == “1_total”)
|> aggregateWindow(every: 1d, fn: last, createEmpty: false)
|> difference()`
The problem is that difference disregards the first record. If I try to keep it by adding keepFirst: true, the difference for the day is 0. I understand that I needed to go to the last day of the previous month, so the first day of the current month would be in.
How can I accomplish getting the last day of the previous month ?
What about doing something like:
my_func = (table=<-, column="_value") => {
output = union(tables: [table |> first(column), table |> last(column)])
|> sort(columns: ["_time"])
|> difference(columns: [column])
return output}
from(bucket: "noaa")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "average_temperature")
|> filter(fn: (r) => r["_field"] == "degrees")
|> filter(fn: (r) => r["location"] == "coyote_creek")
|> aggregateWindow(every: v.windowPeriod, fn: my_func, createEmpty: false)
1 Like
This is what I did. Seems to be working. I don’t have data for last month, so I’m not sure. I have to wait until next month
import “strings”
import “experimental”
month = time(v: “${strings.substring(v: string(v: now()), start: 0, end: 8)}01T00:00:00Z”)
day = experimental.subDuration(d: 1d, from: month)
from(bucket: “Energy”)
|> range(start: day )
|> filter(fn: (r) => r["_measurement"] == “quadro_geral”)
|> filter(fn: (r) => r["_field"] == “0_total” or r["_field"] == “1_total”)
|> aggregateWindow(every: 1d, fn: last, createEmpty: false)
|> difference()
@Anaisdg Do you think it would work ?
@Andre_Martins,
It looks good to me? I’m not sure did it work for you or are you not getting what you expect? It’s hard to tell without seeing your data.
