Influxdb/grafana - difference kWh from IoTaWatt - vs calculated from Watt

In IoTaWatt i’m sending folowing measurements: (along some other stuff :wink: )

  • Verbruik: Watts = Fase1 + Fase2 + Fase3
  • verbruikkWh: kWh = Fase1 + Fase2 + Fase3
  • Export: kWh = (Fase1 abs + Fase2 abs + Fase3 abs + Fase1 + Fase2 + Fase3) ÷ 2 - (Fase1 + Fase2 + Fase3)
    post interval is 10sec and bulk send 6

However if i compare them in grafana there is a big difference between the Total daily kWh directly from IoTaWatt and the one calculated in grafana: At 8PM: from IoTawatt: 19.96kWh vs 20.50kWh calculated.Earlier at the day the difference is bigger.

I’m using:
For the kWh calculated from Watt:
SELECT mean(“Watts”) FROM “iotawatt” WHERE (“ct” = ‘Verbruik’) AND $timeFilter GROUP BY time(1h) fill(null)
In options stat: “Total” and in Time range “now/d”

For the kWh from IoTaWatt:
SELECT sum(“kWh”) FROM “iotawatt” WHERE (“ct” = ‘VerbruikkWh’) AND $timeFilter GROUP BY time(1h) fill(null)
In options stat: “Total” and in Time range nothing (default 1h)

The dashboard is setup for “Today so far”.

The difference between the “export” is even bigger: 17.05kWh (from IoTaWatt) vs 10.33kWh calculated in grafana.
I use:
Calculated in grafana:
SELECT ( (ABS(mean(“Watts”)) + mean(“Watts”))/2 - mean(“Watts”) ) FROM “iotawatt” WHERE (“ct” = ‘Verbruik’) AND $timeFilter GROUP BY time(1h) fill(null)
In options stat: “Total” and in Time range “now/d”

From IotaWatt: the same type as Verbruik. (in IoTaWatt it’s calculated the same way)

Any idea’s what i’m doing wrong?

Can you provide more context? I see this is a three-phase system. Is there a three phase inverter?
Looking at the Export calculation, what are you trying to do? The first part should compute the sum of all of the positive phases. Then you subtract the sum of all of the phases. Are you trying to get the absolute value of the sum of the negative phases?

There is a problem with trying to calculate these mixed sign numbers with the IotaWatt scripts. When you run a script, it operates on the net value of the data over the requested time interval. When you integrate the data over the interval with grafana, you get the sum of those values for each of the ten second measurements in the interval. Those may sound like the same thing, but they are not.

For simplicity sake, lets just deal with a single phase. Lets say the 10 second values over a minute are:

100, -60, -25, -40, 30, 70

The algebraic sum is 75.

If you upload these to grafana with the script: Watts = (fase min 0) abs
you will upload 0, 60, 25, 40, 0, 0 and summing them in grafana you will get 125. Yet if you ask IoTaWatt for the value of that same function for that minute, you will get 0 because the data is the algebraic sum of 75 which processes to 0 in the script.

Take a look at this Tracking separate solar Feed In Tariff

Yes, its a 3 phase system (3F+N). I have 3 ct’s clamped (1 for each fase). They measure positive or negative usage.
I have a single phase solar inverter. On that feed there is also a ct. (only positive off-course).

So far i’ve been exporting the values (watt) to emoncms by adjusting the firmware and sending it via usb to the EmonPi . I send the statRecord.accum1[x] variable every time the datalog runs (been using this since the beginning, so kind of kept using it)

The “mysolarpv” app shows (in kWh) the use (total use) / solar (total generated) / direct use (of the solar) / export (of the solar) and grid.

I wanted to test the influxdb/grafana combo and try to mimic this.

The kWh export for solar / verbruik seem to agree with Emoncms. The calculated value in grafana (from Watts) is a bit higher.(approx 2%), but i guess thats because grafana only gets info every 10 seconds, and the IoTaWatt has more info for this.

In the calculation of the “Export” i’m trying to get the absolute sum of the exported (negative) readings for the CT’s. (Since i’m only exporting on 1 fase, i could have calculated it for 1 fase and added the others: (Fase1 abs + Fase1)/2 - Fase1 + Fase2 +Fase3 should be the same i guess.

Using your example with 1 fase only, it should give (calculated in IoTaWatt):
Fase1 abs: sum of the “values” without signage: 325
Fase1 : sum of the actual values: 75
and the result of the whole thing: 125

Since both values (the watts, and the kWh) are send every 10s, it should give the same results?

I’m not getting this. In the original post, you were comparing your influx/grafana results with the IoTaWatt results:

This latest post seems to be attempting to compare influx/grafana results with the Emoncms Mysolarpv.

These are two completely different things. I’m still trying to understand what you are trying to do with these script functions.

This will yield the value of Fase1 or zero if Fase1 is negative. For example if Fase1 is -20, then you add the absolute value (+20), to the signed value (-20) to get zero, which is still zero when divided by 2. On the other side if Fase1 is +25 you would add the absolute value (+25) to the signed value (+25) to get +50. Divided by two is +25. For what it’s worth, it’s the same as:

(Fase1 max 0). (Select the larger of the two operands)

Now when you subtract Fase1 as in:

You should get the value of Fase1 or zero if Fase1 is positive. Again, for what it’s worth, It’s the same as:

(Fase1 min 0) (Select the smaller of the two operands)

So now when you add Fase2 and Fase3 as in:

You should get the sum of Fase2 and Fase3 minus any negative value for Fase1. I don’t understand the usefulness of this metric.

If you want only the export (negative) on Fase1 then:

(Fase1 min 0) will do that as a negative number and((Fase1 min 0) abs) will return it as a positive number.

If you want the net export for all three Phases then:

((Fase1 + Fase2 + Fase3) min 0) will do that as a negative number. Add the abs function at the end if you want it as a positive number.

To get net import it’s pretty much the opposite with the max function:

((Fase1 + Fase2 + Fase3) max 0)

Assuming your solar is positive, usage is:

(Solar + Fase1 + Fase2 + Fase3) max 0

Not sure how you are evaluating these scripts when exporting to Emoncms through the serial USB port. I would recommend you take another look at that. Exporting these metrics to influx as kWh and adding them, or exporting as Watts and integrating them over time, should allow you to produce similar daily energy numbers to what mysolarpv produces.

Note: had min and max reversed above in original post.

Sorry about the confusion: I have the Emoncms system running since the beginning so i also looked at the differences with the (for me new) Influxdb.
So i’m comparing:

  • the original emoncms value (based on only power (watt) input to emoncms) via serial
  • export of power (watt) to influxdb/grafana and computing the usage (kWh) from that (via “webserver”)
  • export of usage (kWh) to influxdb/grafana (via “webserver”)

The export with the abs etc i based on another post her on the forum, but i didn’t make the link with the operands (min and max) that are available in IoTaWatt.(i just din’t hoover over the function button… :confounded: )
I changed my server output script and have a look at it in grafana when the data of tomorrow is available.

In the mean time i was able to setup grafana to calculate all of the values (grid/solar/import/export,…) i wanted from the power (kWh) export of Solar, grid and usage (=Solar+grid) (i think i could have left the last one out)

UPDATE: works perfect with the new script!