# Create a cumulative average and compare it

Hello,

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

``````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)
``````

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
``````

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).

@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
``````

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.

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})
)
``````