Calculate total where not Null / 0 (totals)

Hi all,

I am streaming some electric usage into influx 2.0 from a IoT device. This is awesome shows me when the spikes of usage are for that device.

Now I show this using a simple query:

from(bucket: "HotWaterPowerConsumption")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "C7F8AFDevice0Room")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

The question - in that query how could I calculate the total for that period (specified in the range). What I am trying to show is for the selected period the total usage is X through the query.

Hello @meaningoflife,
Welcome?
What do you mean by total? The sum? The raw total? or the total of the aggregatewindow?
If its a sum you want, the raw total would look like:

from(bucket: "HotWaterPowerConsumption")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "C7F8AFDevice0Room")
  |> filter(fn: (r) => r["_field"] == "value")
  |> sum()
  |> yield(name: "mean")

Do you have multiple tags? Are you looking for the sum across devices? If so you’d have to group first:

from(bucket: "HotWaterPowerConsumption")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "C7F8AFDevice0Room")
  |> filter(fn: (r) => r["_field"] == "value")
  |> group() 
  |> sum()
  |> yield(name: "mean")

Does that help?

Thank you @Anaisdg for taking the time to reply. So wanted to check some logic with you on the above.

The IoT sensor is logging the actual watts being used in real time (W). What I am trying to do at the query level was to get the sum of watts for the selected period of time (in the time filter)

To answer your question, right now I was using this query for a Bucket that has different measurements (each measurement represents a IoT device) and an associated field (_field) with a value.

What I wanted to end up with was to actually calculate my cost for that period of time. So given that its storing real time Watts, I need to convert the Watts into Kilowatts hours which would be the following calculation:


Kilowatt-Hours: kWh = (watts Ă— hrs) Ă· 1,000.

  1. I have the Watts (which is stored as the value in _field)
  2. I should be able to calculate the hrs based on my search filter in time?
  3. Then I would need to divide by 1000 to get to KwH.
  4. Finally I would then need to multiply the kWh by the unit price from my municipality to get the total cost - we could use * 0.8 for now?.

Any help / example how I could do the above in the query?

In @Anaisdg 's first example, she sums up the value field.

You can then calculate further values from this value if you add maps to your query.

  |> map(fn: (r) => ({ r with _kwh: r._value / 1000 }))
  |> map(fn: (r) => ({ r with _cost: r._kwh * 0.8 })) 

If you need to convert an integer to a float, you can use float(v: r._kwh).

But be aware that you may not have recorded each single watt value. E.g. there could have been very short network outages so that not every value that was measured also got stored in the database. Those missing values will also not be present in the sum and all further calculations.

2 Likes

@LordOfTheSnow thank you!

Thank you both @LordOfTheSnow and @Anaisdg -

@LordOfTheSnow you right I need to convert to Float because if I dont I get:

 runtime error @8:7-8:57: map: type conflict: int != float

So I went and read up about float conversation / method here:

The way I understand it, is to use a map for the value and convert it to the float so did the following (not working but would like to understand why so I can learn):

from(bucket: "HotWaterPowerConsumption")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "C7F8AFDevice0Room")
  |> filter(fn: (r) => r["_field"] == "value")
// Convert the value to float
  |> map(fn: (r) => ({r with _kwh: float(v: r._kwh)}))
  |> map(fn: (r) => ({ r with _kwh: r._value / 1000 }))
  |> group() 
  |> sum()
  |> yield(name: "mean")

The error kind of confuses me:

 runtime error @7:7-7:56: map: type conflict: int != float

Is this because it cant convert the current value to float (could be because MQTT is passing into the influx as string and it is not an int?)

Would appreciate the guidance.

I think it should be like this:

from(bucket: "HotWaterPowerConsumption")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "C7F8AFDevice0Room")
  |> filter(fn: (r) => r["_field"] == "value")
// Convert the value to float
  |> map(fn: (r) => ({ r with _kwh: float(v: r._value) / 1000.0 }))
  |> group() 
  |> sum()
  |> yield(name: "mean")

But yes, the error messages are a joke, for total nerds only. I don’t understand what @7:7-7:56 means either.

Thank you very much that taught me alot! I would do the same map with the same Float conversion to work out the cost:

So instead of:

  |> map(fn: (r) => ({ r with _cost: r._kwh * 0.8 })) 
from(bucket: "HotWaterPowerConsumption")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "C7F8AFDevice0Room")
  |> filter(fn: (r) => r["_field"] == "value")
// Convert the value to float
  |> map(fn: (r) => ({ r with _kwh: float(v: r._value) / 1000.0 }))
// Workout Cost
  |> map(fn: (r) => ({ r with _cost: float(v: r._kwh) * 0.8 }))
  |> group() 
  |> sum()
  |> yield(name: "mean")

That

 |> map(fn: (r) => ({ r with _cost: float(v: r_cost: r._kwh * 0.8  }))

looks far too complicated. Since r._kwh already is a float, you can just map the product of r._kwh * 0.8 to a new variable.

  |> map(fn: (r) => ({ r with _cost: r._kwh * 0.8 })) 
1 Like

Understood thank you!
What I did notice is what you highlighted the periods where it didn’t log is effecting the result.

Example this is a hot water boiler to manage energy costs I turn it off during 10pm - 5am so it will have a period of 0 usage then.

Is there a way to exclude the 0 values at query level or do I need to figure that out at the presentation layer (grafana)?

You can filter out null values from the display in Grafana.

Your problem is the other way round: If you use the boiler but not all values are stored (due to network problems for examle). Then those measurements won’t be in your sum of energy/costs either.

1 Like

So just checking logic here, what you referring to is boiler is still consuming but logging device is off - yes that would be an issue :slight_smile:

Lucky the logger controls the power to the device as well so if the “logger” is off 99% chance the device will be off as well.

Just want to thank you once again!

A clarifying question, once you use the map function and “create a variable” like we did with _kwh you can use it anywhere later in the query, because it’s seen as being defined?

once you use the map function and “create a variable” like we did with _kwh you can use it anywhere later in the query, because it’s seen as being defined?

Well that’s what you do in the line that calculates the cost.

1 Like

@LordOfTheSnow
Quick question to check some logic:

Using this query:

from(bucket: "HotWaterPowerConsumption")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "ApparentPower")
  |> filter(fn: (r) => r["_field"] == "value")
    // Convert the value to float
  |> map(fn: (r) => ({ r with _kwh: float(v: r._value) / 1000.0 }))
  |> map(fn: (r) => ({ r with _cost: r._value * 2.84 })) 
  |> aggregateWindow(every: v.windowPeriod, fn: sum, createEmpty: false)
  |> group() 
  |> sum()
  |> yield(name: "sum")

The result still doesn’t seem to be correct - Let me explain:
ApparentPower - is the measurement that is stored every the hot water device comes on and goes off. Example:

Red Boxes show when the boiler comes on and then the 0’s (purple boxes) are when it turns off.

What I ideally want to do is sum all the value when its on then work out the kWh (as its stored in Watts) as per the above query. When I perform the above query I am getting a number that doesn’t make sense.

Example:

I exported to excel and did the same logic it does appear to sum the value to : 40871 what the query doesn’t seem to be doing is:

    // Convert the value to float
  |> map(fn: (r) => ({ r with _kwh: float(v: r._value) / 1000.0 }))
  |> map(fn: (r) => ({ r with _cost: r._value * 2.84 })) 

Now I am not sure if I have this calculation at the wrong time of the query or if I am missing it at all?

If I manually do it in excel the number should be:
Converting it to kWh - 40.87
Then converting it to a cost should be - $32.69

Found the issue:

|> map(fn: (r) => ({ r with _cost: r._value * 2.84 })) 

I was using the wrong field I was using

r._value 

For the calculation and not:

|> map(fn: (r) => ({ r with _cost: r._kwh * 2.84 })) 

r_kwh for the calculation

The value looks correct now.