Hello,
is there a simple way, to calculate the usage per real day?
What I mean is, I have a db where entries are very spordic.
e.g. for the last 30 days, I have an day 1, 6, 9, 12, 13, 17, 21 and 26 entries in my db.
I can now calculate a daily average as I use the sum of the entries divided by 30.
But I want to have a usage per “real” day. In this example I would divide the sum by 8 (due to the days, where are entries).
Is there a way, to do this in a flux-Query?
Regards,
easy.
Hi @easy and welcome to the forum.
Are days like 2, 5 and 15 completely blank, or with zero values, or some other discernible feature?
Hello @grant1 and thanks. 
they are filled with zeros, data is coming from a simple smart plug meter using grafana for visualiztion.
Currently I’m using following query for the average of the last 30 days: (The map is using due to lack of grafana to label a output “correctly” and for 3 digits after decimal point):
import “math”
from(bucket: “telegraf”)
|> range(start: -30d)
|> filter(fn: (r) => r[“_measurement”] == “tasmota”)
|> filter(fn: (r) => r[“_field”] == “Today”)
|> filter(fn: (r) => r[“sensor_id”] == “smartplug”)
|> aggregateWindow(every: duration(v: 1d), fn: spread, createEmpty: false)
|> mean()
|> map(fn: (r) => ({r with _value: “Tages-Ø: “+string(v: math.round(x: r._value * 1000.0) / 1000.0)+” kWh”}))
I “just” want to exclude the days where no power is consumed to get a “real” average.
Reagards.
I do not have any opportunity to test this, but I believe you can simply add a filter() function before the aggregateWindow() function.
|> filter(fn: (r) => r._value > 0, onEmpty: "drop")
Can you try it out and see if it gives you the correct result?
