Hi,
I’m using time series or single stat to display angle values in the range 0/360.
When the angle varies around the 0, then the mean value calculated by the grafana is wrong.
I’m using grafana with influxdb as data source.
Is there a way to make it calculate the average properly?
I guess a modulo 360 is the solution but I’m struggling right now.
Any tips?
Thanks
Welcome @Ben5687
Can you please share your query? Do you have it working correctly in Influx Data Explorer (vs Grafana)?
Hi @grant2
Here is the query:
for time series:
SELECT mean(“value”) FROM “XXX” WHERE $timeFilter GROUP BY time($__interval) fill(null)
and then using the panel options to calculate the display min/max/mean.
Single stat:
SELECT “value” FROM “XXX” WHERE $timeFilter
and selecting the mean in the panel option:
I’m not using influx data explorer.
I’m a Grafana rookie…
Tried to search the community but didn’t find any topic about that subject.
Thanks
for the time serie, ideally, I’d like to display the angle without the jumps from 0 to 360, like this :
Does anything improve if you change this box to “Last”
no,
i’ve got both running and it doesn’t change the jumps in the time serie, or the wrong averaging calculation
Could you switch to the Table visualization (or toggle to Table view)
and show the data with this query? (with no panel options or calculations set up in Grafana, so we can see the plain data)
SELECT last(“value”) FROM “WINDSilver_TWA_Mean” WHERE $timeFilter
and also (if you have another measurement table besides WINDSilver_TWA_Mean
SELECT last(“value”) FROM “XXX” WHERE $timeFilter
Does the data you just posted look correct?
One of your original questions was:
When the angle varies around the 0, then the mean value calculated by the grafana is wrong.
and in the above data, Grafana should not be doing any calculations, but instead just displaying your data as it exists in InfluxDB.
Just trying to get a clean starting point…
yes, the data is correct
it is the mean calculation that is wrong because it is a angle 0-360
also, for the display, i’d like to avoid the jumps from 0 to 360, as it is a continuons angle, as if 1 was displayed as 361. that’s why I was talking about the MOD.
Can you explain the above? For example, looking at these 5 points from your data:
Are you trying to calculate the mean over all 5 data points? Or perhaps the mean between two consecutive data points? I can see that the 358 value is going to skew the mean, but I am still not clear what Grafana is doing wrong (if anything).
It would probably be worth trying the Modulo operator in your query:
if the dataset was not an angle, the mean calculation by grafana would be OK.
but as it is an angle betwen 0 and 360, it needs some “calculation”.
That’s why I was thinking of the modulo, but not sure how to implement it in the query.
also, i’ve read this thread: Averaging values(they are angles) which are wrapping in o to 360 - Mathematics Stack Exchange
Hi @Ben5687
Had to revisit all this stuff since it’s been a few years!
Using the same 5 data points previously mentioned, would this be correct in your opinion?
using these formulae:
If yes, then I kinda doubt Grafana or InfluxQL is going to be the tool to use here, but it’s all do-able using Flux or some other scripting that can be done on the angle values before ingesting into InfluxDB.
1.) You are mixing single stat with time series panel - both need different approach.
2.) Calculating avg in the singlestat panel from already aggregated values SELECT mean(“value”) FROM “XXX” WHERE $timeFilter GROUP BY time($__interval) fill(null) is math disaster - because result is not avg of whole set
3.) How smooth is the result depends on time aggregation. Don’t use magic $_interval variable, but define own value (variable) there, which will fit your expectations. It will be different if query returns 1min aggregation or 15min aggregation, e.g.:
SELECT mean(“value”) FROM “XXX” WHERE $timeFilter GROUP BY time(1ml) fill(null)
vs
SELECT mean(“value”) FROM “XXX” WHERE $timeFilter GROUP BY time(15m) fill(null)
that’s not correct
here is how i calculate the correct average (SI is IF):
I’ll dig in scripting with influx then
i’ll give a try, thanks.
But it won’t help on the average calculation.
Avg is just SELECT mean(“value”) FROM “XXX” WHERE $timeFilter - but that’s only for singlestat panel type - result is single number, this is not for time series panel.
yes, I know, but as said, I’m trying to calculate a mean of an angle range between 0 and 360 displayed as a single stat.
that’s what i’d like to implement.
