Hi, In my home automation setup I have some sensors that are spitting out kWh data in a rather caotic way. Most data are positive, but some are negative, and I’m pretty sure my ovens do not produce any power to the grid 
I don’t have any control over the source, so I have to fix the data in InfluxDB (that I don’t know anything about) or fix it in Grafana for correct viewing.
Any idea how to make all negative numbers positive?
In Grafana, for your Influx query, is it written in InfluxQL or Flux? You should be able to handle this using the absolute value function.
Post the query here if you cannot get it going (click the pencil icon to view the full query syntax).
I guess it’s InfluxQL, this is my query:
SELECT difference(mean("value")) FROM "kWh" WHERE ("entity_id" = 'soc_kontor_consumption_today') AND $timeFilter GROUP BY time(1h) fill(0)
When I used this query:
SELECT ABS(difference(mean("value"))) FROM "kWh" WHERE ("entity_id" = 'soc_kontor_consumption_today') AND $timeFilter GROUP BY time(1h) fill(0)
The output got pretty strange:
(The screenshot is from the same graph as the left graph in my original post.)
I use this transform to get the result:
OK, it looks like the abs() function is correctly placed in the query, but that when you clicked the pencil icon (to display the syntax of the query), it put both queries as visible, since it appears you have 2 queries in the legend (kwH.abs and NOK.mean). To make one of those invisible / visible on the graph, toggle (click) the eye icon on the right.
You can also use non_negative_difference. In that case negative values would be ignored (set to zero) rather than converted to positive
Thank you 
If you look closer, I have an transformation to multiply kWh and NOK to get the usage cost. If I choose to view only the kWh, everything looks OK, but I won’t get the calculation I’m after. It looks like something strange is happening in the transformation.
Have been looking around in my panel for some time now, and I’m pretty lost. For some strange reason it looks like how query B is displayed depends on query A (My transform is completely turned off).
This the result if A and B is turned on:
This is the result if A is on and B is off:
And this is the result if A is off and B is on:
Any idea what is going on?
I think you are getting close. Maybe to make it easier to troubleshoot, display the legend and then just click on each item, like this:
Server 1 only:
Server 2 only:
Server 1 * Server 2 (binary transformation operation, same as what you are wanting to do)
Hmmm, you need to check your sensor data. Are you using single or three-phase ovens?
Are you sure you are using the correct voltage and current for each phase ie red voltage and red current, blue voltage and blue current and red voltage, and red current?
What is this sensor you refer to?
You cannot mix these and also make sure the current sensors are connected with the correct polarity.
Ovens are a resistive load and should only draw positive power and NEVER negative!
This suggests you have a polarity or phase error or both.
Note junk in = junk out!
Also if your sensors are returning kWh what is their integration period?
In your query, you are grouping readings by the hour which means it will take the individual kWh readings between every hour and sum them.
Having grouped them you then try and take the difference of their mean, which makes no sense at all.
Please just return the grouped values and plot them. Do not do any means different etc
Draw a plot of the raw data without any grouping and one with it grouped and lets see what you get.
Also if you are trying to add all your oven using a single query then you have to group and do sum, not difference. It is not making sense and using ABS does nothing as the difference in any case only returns the difference (less 1) between any 2 values and is always positive.
The ovens are single phase wall mounted “smart” heaters with built in sensors. As far as i know they send kWh data once every hour, but I might be wrong. I think I hust found the reason for the negative values. The kWh data I use are kWh usage accumulating trough 24 hours (the reason I use differense). At 00.00 every night the kWh data resets to zero.
I just realized I have a problem that is even worse: I have a lot of sensors that behaves like this where I have no control of when they reset the kWh counter. Some resets every 24h, some every 30d, some every month and some every year. Some of them resets at a set time and some at a time since they where first started. How do I get correct statistics for these sensors?
probably need to look at a combination of non-negative derivative and fill(). There’s more info in this SO thread: InfluxDB/Grafana return counter starting at zero - Stack Overflow
Ok that’s great as I suspected you had a problem elsewhere.
With normal meters, they maintain a real-time clock all of which should be synchronized to the mains so that they have a common time reference. In your case, you will have to rely on or ensure that the time at each device is set as accurately as possible.
Meters also have accumulative registers and load profile registers which may be reset every say 15/30 minutes
As you note it appears each device appears to have different times as well as different reset intervals.
This is not a problem and one which every utility faces when analog meters rollover. This requires you to first clean your sensor data and rather make each appear as they are ALL accumulative data only. This will then make all meters appear as if they are all accumulative data only. This is overcome by doing a running sum on all the data for every meter.
This will make the data for each meter appear as a continuously increasing line of energy for each and every meter. This will then remove any issues related to rest or rollover interval.
The next is to account for the time skew in each meter, to do this you will have to make one sensor time series start time the reference time and then adjust each of the other time series by the same amount. Best to choose the earliest first sample of all the meters.
So now you will have resolved the time skew but each sensor might have data gaps including the reference meter. So what you now have to do is do a join of all the adjusted time series to get a master time series that includes the timestamps for each and every sensor.
So now you have a common timestamp that includes all the adjusted timestamps for all readings in all sensors and all energy data for each of these are all accumulative data.
Now they may be summed using a 1 hour group and the difference in each 1 hour summation used as you are doing
A AAAA AAAA A AA A AAAAAAAAAA
BB BBBBBBBBBBBBBBBBBBBBBBB BBBBBBB BBBBB
CCCCCCCCCCC CCCCCCCCCCCC CCCCCCCCCC
first remove time skew use series A as reference
A AAAA AAAA A AA A AAAAAAAAAA
BB BBBBBBBBBBBBBBBBBBBBBBB BBBBBBB BBBBB
CCCCCCCCCCC CCCCCCCCCCCC CCCCCCCCCC
Now Do JOIN
ABCAAAABBBBAAAABABAAABBBBBCBBBBBAAAAAAAAAA
BB BBBBBBBBBBBBBBBBBBBBBBB BBBBBBB BBBBB
CCCCCCCCCCC CCCCCCCCCCCC CCCCCCCCCC
So now you have a common time series that accounts for the time skew as well as missing gaps.
The only problem is now the gaps and one would have to interpolate. However, this will autocorrect when all series have data at a common timestamp
Anyway hope this helps.
The skew and missing gaps may be readily done, but easily rectified, but is doable.
The above might be far easier to do using Tableau Public.
The best is to use meters that can be linked with ethernet and have synched time stamps but missing data still is a problem.
Normally devices allow one to choose the rest period. Some have daily, monthly and annual registers as well. Send me the device type and manual and I can have a peek.
DM me to gregdiana1@gmail.com.
Thank you a lot!
I have to admit I have no idea how to implement what you proposed as I am pretty new to this and I’m not a coder of any kind 
First of all, I cannot use Tableau Plublic as it is not supported by my home automation solution Home Assistant.
Second, I have no control over how and when my sensors reports as they are basic IoT devices (Mill Heaters, Tibber Pulse, Fibaro Plug, Samsung Washer and Dryer and some more) so I’m pretty locked to what they deliver as far as I can see. The only thing I guess may be time synced and not just using an internal clock with some occasional NTP adjustments is the Tibber Pulse. Some of the systems does not even report close to near real time and I have a delay caused by hard coded polling intervals to different cloud services that in some systems are five minutes.
On the brighter side, I don’t need very high accuracy as I’m not running a SCADA or DCS system (although it may look like one). In Gafana I use the data to visualize data to analyse how to use less power when electricity prizes are high and less power in total (and I love having great graphs when wife is about to be nagged).
Ha, ha no problem. Does your Tibber Pulse read the energy consumption of your house and all these devices?
You most probably use the docker with node-red, influxdb and Grafana?
But you can get node-red to output that data to an excel file and then use either excel or Tableau Public to graph and analyse the data from the excel files.
Tibber pulse has an API that node-red uses to get the meter data so it is possible and you may use the tibber-pulse data as the reference as it will be synched to the meter I presume you have the TP connected to the HAN of the meter?
If you have the data in excel then you can also use excel data to show graphs in Grafana
Unfortunately like you say is that many IoT devices do their own thing and the real work is integrating and getting them to be interoperable.
Unfortunately, I am in South Africa so do not have these systems locally. So I am unable to nag the wife 
Good luck and all the best.
Here is something I found useful in influxdb. IT RESOLVES THE ROLLOVER / RESET issue and makes it a single accumulative value ! 
And here is the running sum
What we need to realise is that these are not new requirements and others have most probably done this. We just have to find them!
This topic was automatically closed after 365 days. New replies are no longer allowed.
