Create a cumulative average and compare it

1

Hello,

With my energy supplier, I’m billed from the 17th to the 16th.

So I made this script and chart to adapt.

import "timezone"
import "date"

option location = timezone.location(name: "Europe/Paris")

from(bucket: "GIN")
  |> range(start: 2024-03-17T00:00:00Z, stop: 2024-04-16T12:00:00Z) 
  |> filter(fn: (r) => r["_measurement"] == "ConsoElec")
  |> cumulativeSum(columns: ["_value"])
  |> map(fn: (r) => ({ r with dayOfMonth: uint(v: date.monthDay(t: r._time)) }))
  |> fill(column: "dayOfMonth", usePrevious: true)

Capture d’écran 2024-06-18 à 11.37.38
Capture d’écran 2024-06-18 à 11.37.381352×602 107 KB

I’ve got several queries that interrogate predefined dates, and the day number is added in a column to create an XY graph.

With Flux, I try to create an average value for each day, but I don’t have enough time for each value.
So I can’t insert it into the XY to see more easily which curve is above or below the average.

import "timezone"
import "date"

option location = timezone.location(name: "Europe/Paris")

get_values = (start, stop) => 
  from(bucket: "GIN")
    |> range(start: start, stop: stop) 
    |> filter(fn: (r) => r["_measurement"] == "ConsoElec")
    |> cumulativeSum(columns: ["_value"])
    |> map(fn: (r) => ({ r with dayOfMonth: uint(v: date.monthDay(t: r._time)) }))
    |> fill(column: "dayOfMonth", usePrevious: true)

valeur1 = get_values(start: 2024-05-16T18:00:00Z, stop: 2024-06-16T12:00:00Z)
valeur2 = get_values(start: 2024-04-16T18:00:00Z, stop: 2024-05-16T12:00:00Z)

combined = union(tables: [valeur1, valeur2])

daily_mean = combined
  |> group(columns: ["dayOfMonth"])
  |> mean(column: "_value")
  |> yield(name: "daily_mean")

daily_mean

Capture d’écran 2024-06-18 à 11.39.40
Capture d’écran 2024-06-18 à 11.39.40835×213 26.9 KB

In addition to this, I’d like to create an indicator that establishes the difference between the current month’s cumulative total and the average cumulative total (comparable to today).

Thanks for your help

2

@Yohan You’re query is so close. When you group by dayOfMonth and calculate the daily mean, each day is represented by a single table. You need to “ungroup” into a single table and then sort by dayOfMonth:

// ...

daily_mean = combined
  |> group(columns: ["dayOfMonth"])
  |> mean(column: "_value")
  |> group()
  |> sort(columns: ["dayOfMonth"])

daily_mean
3

Thanks, it works.

How do I subtract the cumulative value by the average reference value to create an index.

For example, it’s the 25th of the month and the average reference (the one we’ve just made) is 48 and the value for the current period on the 25th is 40, so the index is +8. It would be negative if we were higher.

4

You have a few options:

  • Create a custom function that returns the average per day of the month. Once the daily value and the daily average are in each row, you can just use map to subtract the average from the daily value.
  • Use a join to join on the day of the month and then subtract the mean from the daily value.

Custom function

// ...

daily_mean = combined
  |> group(columns: ["dayOfMonth"])
  |> mean(column: "_value")
  |> group()
  |> sort(columns: ["dayOfMonth"])

getDailyAvg = (day) => {
    dailyAvg =
        (daily_mean
            |> filter(fn: (r) => r.dayOfMonth == day)
            |> findColumn(fn: (key) => true, column: "_value"))[0]

    return dailyAvg
}

// Get the index
valeur1
    |> map(fn: (r) => ({r with _value: r._value - getDailyAvg(day: r.dayOfMonth}))

Join

import "join"

// ...

join.left(
    left: valeur1 |> group(),
    right: daily_mean  |> group(),
    on: (l, r) => l.dayOfMonth == r.dayOfMonth,
    as: (l, r) => ({l with _value: l._value - r._value})
)