How to generate stacked bar comparing values between two years data on monthly interval

Grafana
1

Hi all,
I am storing hourly usage data in influxdb as below
building,deviceId=B11-D00074,name=Electricity Value=0.8 1718150400000000000
I want to create a stacked bar as below. Can someone help me with query using influx v2 flux query.
x axis contains Month names,
Y axis contains sum by monthly for 2 years (ie for example 2023,2024)

image
image2099×451 34.6 KB

Regards,

2

An example of comparison between today and yesterday:

Compare690x427
Compare690x427944×585 29.2 KB

import "date"
import "experimental/date/boundaries"

today = from(bucket: "my-bucket")
  |> range(start: today(), stop: now())
  |> filter(fn: (r) => r["_measurement"] == "my_measurement")
  |> filter(fn: (r) => r["_field"] == "my_field")
  |> map(fn: (r) => ({ r with hour: date.hour(t: r._time)}))  
  |> group(columns: ["hour"], mode:"by")
  |> mean(column: "_value") 
  |> group()

  yesterday = from(bucket: "my-bucket")
  |> range(start: boundaries.yesterday().start, stop: date.add(d: -1d, to: now()))
  |> filter(fn: (r) => r["_measurement"] == "my_measurement")
  |> filter(fn: (r) => r["_field"] == "my_field")
  |> map(fn: (r) => ({ r with hour: date.hour(t: r._time)}))  
  |> group(columns: ["hour"], mode:"by")
  |> mean(column: "_value") 
  |> group()

  join(tables: {key1: today, key2: yesterday}, on: ["hour"], method: "inner")
  |> rename (columns: {_value_key1 : "Today", _value_key2 : "Yesterday"})

The idea is to have two queries for period 1 and period 2 (today and yesterday in my example), join them on requred step (hour in my example)

hour columns was converted to string by Grafana means:

transformation
transformation708×295 50.6 KB

Another way to do it using aggregationWindow, subDuration and timeShift (result would be the same):

import "date"
import "experimental"

today = from(bucket: "my-bucket")
  |> range(start: today(), stop: now())
  |> filter(fn: (r) => r["_measurement"] == "my_measurement")
  |> filter(fn: (r) => r["_field"] == "my_field")
  |> aggregateWindow(every: 1h, fn: mean, timeSrc: "_start")  

yesterday = from(bucket: "my-bucket")
  |> range(start: experimental.subDuration(d: 1d, from:today()), stop: experimental.subDuration(d: 1d, from:now()))   
  |> filter(fn: (r) => r["_measurement"] == "my_measurement")
  |> filter(fn: (r) => r["_field"] == "my_field")
  |> aggregateWindow(every: 1h, fn: mean, timeSrc: "_start")
  |> timeShift(duration: 1d)

  join(tables: {key1: today, key2: yesterday}, on: ["_time"], method: "inner")
  |> rename (columns: {_value_key1 : "Today", _value_key2 : "Yesterday"})
2 Likes
3

Thanks ebabeshko. It helped me a lot.
I modified the query to show monthly. But the query did not show all months from last year. Is there any way to fix this ?

import "date"
import "experimental/date/boundaries"

monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]

lastyear = from(bucket: "production")
  |> range(start: 2023-01-01T00:00:00Z, stop: 2023-12-31T23:59:59Z)
  |> filter(fn: (r) => r._measurement == "building")
  |> filter(fn: (r) => r["deviceId"] == "B1-D00029")
  |> filter(fn: (r) => r._field == "Value")
  |> map(fn: (r) => ({
    r with 
    month: date.month(t: r._time),
    monthName: monthNames[date.month(t: r._time) - 1]
  }))
  |> group(columns: ["month"], mode: "by")
  |> mean(column: "_value") 
  |> group()

thisyear = from(bucket: "production")
  |> range(start: 2024-01-01T00:00:00Z, stop: 2024-12-31T23:59:59Z)
  |> filter(fn: (r) => r._measurement == "building")
  |> filter(fn: (r) => r["deviceId"] == "B1-D00029")
  |> filter(fn: (r) => r._field == "Value")
  |> map(fn: (r) => ({
    r with 
    month: date.month(t: r._time),
    monthName: monthNames[date.month(t: r._time) - 1]
  }))
  |> group(columns: ["month"], mode: "by")
  |> mean(column: "_value") 
  |> group()

join(tables: {key1: thisyear, key2: lastyear}, on: ["month"], method: "inner")
  |> rename(columns: {_value_key1: "This Year", _value_key2: "Last Year"})
  |> sort(columns: ["month"])  
  |> map(fn: (r) => ({
    r with monthName: monthNames[r.month - 1]
  }))
  |> yield(name: "monthly_consumption")

image
image1757×516 15.7 KB

4

That’s because of inner join is used, and there is no data for July-December of this year yet.

Use outer join instead:

import "join"

...

join.full(
    left: lastyear,
    right: thisyear,
    on: (l, r) => l.month == r.month,
    as: (l, r) => {
        return {"This Year" : r._value, "Last Year" : l._value, month : l.month}
    }
    )
  |> map(fn: (r) => ({
    r with monthName: monthNames[r.month - 1]
  }))    
  |> drop(columns : ["month"])
  |> yield(name : "monthly_consumption")

Result should be similar to this:

bar chart
bar chart1045×475 58.2 KB

Table view:

table view
table view602×665 17.4 KB

2 Likes
5

Hi, Thanks once again.
See to be some where I am missing syntax. can you please validate

import "date"
import "experimental/date/boundaries"

monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]

lastyear = from(bucket: "production")
  |> range(start: 2023-01-01T00:00:00Z, stop: 2023-12-31T23:59:59Z)
  |> filter(fn: (r) => r._measurement == "building")
  |> filter(fn: (r) => r["deviceId"] == "B1-D00029")
  |> filter(fn: (r) => r._field == "Value")
  |> map(fn: (r) => ({
    r with 
    month: date.month(t: r._time),
    monthName: monthNames[date.month(t: r._time) - 1]
  }))
  |> group(columns: ["month"], mode: "by")
  |> sum(column: "_value") 
  |> group()

thisyear = from(bucket: "production")
  |> range(start: 2024-01-01T00:00:00Z, stop: 2024-12-31T23:59:59Z)
  |> filter(fn: (r) => r._measurement == "building")
  |> filter(fn: (r) => r["deviceId"] == "B1-D00029")
  |> filter(fn: (r) => r._field == "Value")
  |> map(fn: (r) => ({
    r with 
    month: date.month(t: r._time),
    monthName: monthNames[date.month(t: r._time) - 1]
  }))
  |> group(columns: ["month"], mode: "by")
  |> sum(column: "_value") 
  |> group()


join.full(
    left: lastyear,
    right: thisyear,
    on: (l, r) => l.month == r.month,
    as: (l, r) => {
        return {"This Year" : r._value, "Last Year" : l._value, month : l.month}
    }
)

  |> map(fn: (r) => ({
    r with monthName: monthNames[r.month - 1]
  }))    
  |> drop(columns : ["month"])
  |> yield(name : "monthly_consumption")

Error

invalid: error @35:1-35:5: expected { A with full: ( as: (l: {B with month: D, _value: C}, r: {E with _value: F}) => {month: D, This Year: F, Last Year: C}, left: stream[G], on: (l: {H with month: I}, r: {J with month: K}) => bool, right: stream[L], ) => stream[{M with month: int}], } (record) but found (<-tables: N, ?method: string, ?on: [string]) => stream[O] (function)
6

You are missing this:

7

Hi,
Thanks a lot. You helped and made my day.
I think its got fixed. :pray: :pray: :pray:
I really appreciate your response. This discussion really made me to learn something new.

Regards,
Surendra