Difference() is not returning the difference

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)

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.