Gauge: Replace empty query with value from another query

[lutz85]

Hello! Thanks for you help already.
Certainly my way of thinking might be biased / blocked by my previous attempts of solving it. So I am also happy for any other ideas.

• What Grafana version and what operating system are you using?

  • Grafana v11.2.1 (086b47f098) within Home Assistant. I have InfluxQL with Influx v1 as data source.

• What are you trying to achieve?

  • I want to set a Gauge to a (percentage) value (0…100% or 0 to 1). The normal query is working ok. The signal is binary (0/1) and by calculating the MEAN of it I basically have the usage percentage that I want. However I try to make the gauge work as well if there are no values within the user-selected timewindow.

• How are you trying to achieve it?
  Therefore I added the following queries / expressions. The base query is forwardfilled however if there are NaN values at the beginning of the timewindow it would naturally induce an error in the final percentage outtput. To tackle this I introduced the other queries. Please bear with me - it sounds complicated because I probably overcomplicated it, but the actual task is pretty straightforward.

Base query: “raw”
SELECT last(“value”) FROM “autogen”.“Wohnzimmer_Heiz_Status_Stellgroesse_alle_3” WHERE $timeFilter GROUP BY time($__interval) fill(previous)

Other Queries:
“prev”: This gets the last value BEFORE the user-timewindow (idea is to use this to fill all the NaNs that still exist within the base query)
SELECT last(“value”) FROM “Wohnzimmer_Heiz_Status_Stellgroesse_alle_3” WHERE time < $__from * 1000000
→ is a series that holds one value 0 / 1
“prevval”: is a Reduce expression to to be able to reduce the prev to a single constant (to be used like a factor for multiplication) → is a number 0/1
“nans_filled_prev”: Using the is_null math expression to find any NaNs from base query and then multiply those by prevval
expression: is_null($raw)*$prevval

Then I have two REDUCE (Sum) expressions where I basically sum up the two time series: “raw” and “nans_filled_prev”. sum_raw and sum_prev
Also there is an expression that counts the datapoints: As you remember I basically try to calculate a mean, I therefore also make an expression that counts the datapoints: Reduce (Count) on the nans_filled_prev. Name: count_dp

So finally I calculate my heating percentage as:
“final_percentage”:
($sum_raw + $sum_prev) / $count_dp * 100.0

• What happened?

  • This works well if there are ANY datapoints within the timewindow. If there are “No data” / empty array it obviously shows No Data (fair enough). But instead of showing No data I want to set the Gauge to the value of prevval (which is the physically / mathematically correct thing to do). So it should be really easy… : If final_percentage has any result / is not NaN then use it. Otherwise use prevval

• What did you expect to happen?

  • I tried math expressions, I tried The legacy expressions. I tried overrides, I tried transformations. I can’t get it done.

Is it really such a difficult thing I am trying to achieve here? Please help me. Maybe I took a wrong turn early and this can all be fixed by adjusting my queries. Or can be fixed with a simple transformation? I don’t know. So I try to solve it now on a level where I have two values and grafana should just pick the first of them that is not NaN. maybe instead this problem can be fixed already on a time series level beforehand. e.g. use the base query and perform another fill operation on it where prevval is used… But I fail with the execution on either of them.

Dropped approaches :
(outside of the box) set KNX system up so it sends regular telegrams instead of “on change” -telegrams. Is tricky for this particular actor and would create a lot of unnessary data basically.

[yosiasz]

to keep things simple, please post sample data as csv and the expected output.

I think you are hitting limitations of this influxql query language as well as stock gauge plugin

[lutz85]

Thanks for your quick reply! Good idea to break the complexity of my description down using examples & data.
I can add a CSV tomorrow - I am only at the cellphone right now. So for now I can provide those screenshots:

This is what the data looks like (zeros and ones like a step function). We can trust the forwardfilled query from the first not-NaN datapoint onwards. But within the timefilter we don’t know if the signal before the first not-NaN dp was a 0 or a 1. and this induces an error when we calculate the mean over the timefilter window. Hence the additional queries „prevval“ to solve this.

My final goal is to have an accurate mean over timefilter window like this: a single gauge 0…100%

So the induced error is basically solved with the queries described in my original message. But the big problem is when there is not a single dp within the timefiltered query. In this case I want to set gauge to prevval instead of no data

[yosiasz]

@lutz85

This is what the data looks like (zeros and ones like a step function). We can trust the forwardfilled query from the first not-NaN datapoint onwards. But within the timefilter we don’t know if the signal before the first not-NaN dp was a 0 or a 1. and this induces an error when we calculate the mean over the timefilter window. Hence the additional queries „prevval“ to solve this.

Good idea to break the complexity of the problem statement down using csv data. See you tomorrow

[lutz85]

Hello, please find attached the data csv (extracted from Influx Chronograf with SELECT “value” FROM “knx”.“autogen”.“Wohnzimmer_Heiz_Status_Stellgroesse_alle_3” WHERE time > :dashboardTime: AND time < :upperDashboardTime: )
csv file: https://file.io/YPEwsLCB9p1a

(Classic Forwardfill Scenario)

around line 458 there is a >24h gap in the data. this can happen when there was simply no signal change for a longer period.

(since the last dp was on 2024-10-11T23…, value 1 → value 1 is physically valid for the whole duration until the next dp on 2024-10-13T15…)

If the user-selected timeperiod is partially in this gap, let’s say from 2024-10-13T10 to 2024-10-13T20 , the Mean value (“percentage”) that the gauge is showing would be wrong (because ffill does not work when there are NaNs at the beginning of the timewindow)

Using the previous “1” from Line 457 is already working with the current “hack” implemented :
Query “prev”:

→ this works fine

HOWEVER, my big problem is now still when the user-selected timewindow is entirely within the data gap, e.g. 2024-10-13T00 to 2024-10-13T10 I just get no data and gauge does not work. Instead gauge should just show the previous value from the “hack”. My longer initial post describes how I tried to deal with it on a “single value” level, but if it can be solved on a timeseries level, would also be good. I did not manage to do it. One simple solution for me would be to do something like (if “raw” query is empty → use prevval) but that’s maybe not how grafana works / how it can be used. I hope I could make the problem more clear with this post. Thanks again for your help!

[grant2]

What do you have in the left pane of Grafana under the Calculation drop-down? Does changing that value to Last* or Last change the output?

[lutz85]

Hello! It was set to mean, but I can also set it to Last* / Last without that it changes anything.

Another idea: is there a way in which I can modify my query so that it never returns an empty array (even if user selects a time-window with zero datapoints). I think the empty array is the thing that kills me here, because all subsequent expressions etc. are not working properly on “no data”. I guess I am limited by InfluxQL on this point. I read something about a “vector(0)” I can add to my query but no sure if this works with InfluxQL…

Screenshot of my current queries:

[yosiasz]

seems like file is not available. you are still polluting your posts with fluff that distract from solving your issue. please simply post sample data. you can even do it as follows, inline csv

date,value
2024-10-11T23:08,1
2024-10-11T23:10,1
2024-10-11T23:11,1
2024-10-13T23:08,1

[lutz85]

true, I don’t have a clear path yet how to tackle this. that’s why I try to provide surrounding information. maybe it’s better to focus on a few concrete paths, e.g. is it possible to make a compact if statement in grafana (if query result is empty, use value from other query), is it possible to assign a default value to a query in grafanam, etc.

please find attached the data.

inline data:

time,Wohnzimmer_Heiz_Status_Stellgroesse_alle_3.value
2024-10-09T13:35:35.831,0
2024-10-09T13:35:39.875,1
2024-10-09T14:00:34.777,0
2024-10-09T14:00:56.522,1
2024-10-09T14:15:48.341,0
2024-10-09T14:16:06.536,1
2024-10-09T14:30:40.600,0
2024-10-09T14:31:16.416,1
2024-10-09T14:45:50.709,0
2024-10-09T14:46:26.601,1
2024-10-09T15:00:42.894,0
2024-10-09T15:01:36.478,1
2024-10-09T15:15:49.249,0
2024-10-09T15:16:46.364,1
2024-10-09T15:30:41.000,0
2024-10-09T15:31:56.321,1
2024-10-09T15:45:47.440,0
2024-10-09T15:47:06.289,1
2024-10-09T16:00:36.292,0
2024-10-09T16:02:16.350,1
2024-10-09T16:15:42.778,0
2024-10-09T16:17:26.382,1
2024-10-09T16:30:49.269,0
2024-10-09T16:32:36.449,1
2024-10-09T16:45:55.650,0
2024-10-09T16:47:46.333,1
2024-10-09T17:01:02.146,0
2024-10-09T17:02:56.353,1
2024-10-09T17:16:12.157,0
2024-10-09T17:18:06.384,1
2024-10-09T17:31:18.575,0
2024-10-09T17:33:16.330,1
2024-10-09T17:46:43.168,0
2024-10-09T17:48:26.932,1
2024-10-09T18:01:32.230,0
2024-10-09T18:03:37.685,1
2024-10-09T18:16:21.634,0
2024-10-09T18:18:48.322,1
2024-10-09T18:31:25.136,0
2024-10-09T18:33:58.949,1
2024-10-09T18:46:31.892,0
2024-10-09T18:49:09.112,1
2024-10-09T19:01:17.176,0
2024-10-09T19:04:19.152,1
2024-10-09T19:16:23.115,0
2024-10-09T19:19:29.191,1
2024-10-09T19:31:26.052,0
2024-10-09T19:34:39.149,1
2024-10-09T19:46:50.679,0
2024-10-09T19:49:49.151,1
2024-10-09T20:01:57.088,0
2024-10-09T20:04:59.074,1
2024-10-09T20:16:59.545,0
2024-10-09T20:20:09.098,1
2024-10-09T20:32:05.996,0
2024-10-09T20:35:19.092,1
2024-10-09T20:47:30.578,0
2024-10-09T20:50:28.993,1
2024-10-09T21:02:37.023,0
2024-10-09T21:05:38.979,1
2024-10-09T21:17:46.971,0
2024-10-09T21:20:49.041,1
2024-10-09T21:32:53.004,0
2024-10-09T21:35:58.959,1
2024-10-09T21:47:59.344,0
2024-10-09T21:51:09.200,1
2024-10-09T22:03:06.638,0
2024-10-09T22:06:19.884,1
2024-10-09T22:17:52.559,0
2024-10-09T22:21:30.523,1
2024-10-09T22:32:59.639,0
2024-10-09T22:36:41.303,1
2024-10-09T22:48:06.773,0
2024-10-09T22:51:51.883,1
2024-10-09T23:03:13.709,0
2024-10-09T23:07:02.418,1
2024-10-09T23:18:17.276,0
2024-10-09T23:22:13.110,1
2024-10-09T23:33:24.454,0
2024-10-09T23:37:23.799,1
2024-10-09T23:48:31.465,0
2024-10-09T23:52:34.278,1
2024-10-10T00:03:38.084,0
2024-10-10T00:07:44.785,1
2024-10-10T00:18:59.560,0
2024-10-10T00:22:55.136,1
2024-10-10T00:34:05.971,0
2024-10-10T00:38:05.057,1
2024-10-10T00:49:33.458,0
2024-10-10T00:53:14.809,1
2024-10-10T01:04:39.748,0
2024-10-10T01:08:24.694,1
2024-10-10T01:19:46.136,0
2024-10-10T01:23:34.588,1
2024-10-10T01:34:56.173,0
2024-10-10T01:38:44.484,1
2024-10-10T01:50:20.122,0
2024-10-10T01:53:54.461,1
2024-10-10T02:05:29.992,0
2024-10-10T02:09:04.312,1
2024-10-10T02:20:39.910,0
2024-10-10T02:24:14.278,1
2024-10-10T02:35:46.304,0
2024-10-10T02:39:24.183,1
2024-10-10T02:50:56.023,0
2024-10-10T02:54:33.940,1
2024-10-10T03:06:05.905,0
2024-10-10T03:09:43.782,1
2024-10-10T03:21:12.261,0
2024-10-10T03:24:53.656,1
2024-10-10T03:36:22.216,0
2024-10-10T03:40:03.611,1
2024-10-10T03:51:32.124,0
2024-10-10T03:55:13.483,1
2024-10-10T04:06:42.157,0
2024-10-10T04:10:23.550,1
2024-10-10T04:22:09.931,0
2024-10-10T04:25:33.613,1
2024-10-10T04:37:19.967,0
2024-10-10T04:40:43.664,1
2024-10-10T04:52:29.770,0
2024-10-10T04:55:53.633,1
2024-10-10T05:07:39.918,0
2024-10-10T05:11:03.607,1
2024-10-10T05:22:49.875,0
2024-10-10T05:26:13.566,1
2024-10-10T05:37:59.885,0
2024-10-10T05:41:23.598,1
2024-10-10T05:53:09.794,0
2024-10-10T05:56:33.574,1
2024-10-10T06:08:19.885,0
2024-10-10T06:11:43.601,1
2024-10-10T06:23:30.001,0
2024-10-10T06:26:54.076,1
2024-10-10T06:38:40.705,0
2024-10-10T06:42:04.618,1
2024-10-10T06:53:51.299,0
2024-10-10T06:57:15.221,1
2024-10-10T07:09:02.019,0
2024-10-10T07:12:25.917,1
2024-10-10T07:24:12.673,0
2024-10-10T07:27:36.499,1
2024-10-10T07:39:23.264,0
2024-10-10T07:42:47.160,1
2024-10-10T07:54:33.752,0
2024-10-10T07:57:57.648,1
2024-10-10T08:09:44.163,0
2024-10-10T08:13:07.865,1
2024-10-10T08:24:54.211,0
2024-10-10T08:28:17.906,1
2024-10-10T08:40:04.176,0
2024-10-10T08:43:27.870,1
2024-10-10T08:55:31.789,0
2024-10-10T08:58:37.801,1
2024-10-10T09:10:45.717,0
2024-10-10T09:13:47.649,1
2024-10-10T09:25:55.531,0
2024-10-10T09:28:57.396,1
2024-10-10T09:41:05.398,0
2024-10-10T09:44:07.325,1
2024-10-10T09:56:15.116,0
2024-10-10T09:59:17.237,1
2024-10-10T10:11:28.734,0
2024-10-10T10:14:27.187,1
2024-10-10T10:26:38.609,0
2024-10-10T10:29:37.046,1
2024-10-10T10:41:48.541,0
2024-10-10T10:44:46.983,1
2024-10-10T10:57:02.066,0
2024-10-10T10:59:56.816,1
2024-10-10T11:12:12.003,0
2024-10-10T11:15:06.915,1
2024-10-10T11:27:21.896,0
2024-10-10T11:30:16.772,1
2024-10-10T11:42:35.107,0
2024-10-10T11:45:26.550,1
2024-10-10T11:57:45.137,0
2024-10-10T12:00:36.467,1
2024-10-10T12:12:55.730,0
2024-10-10T12:15:47.184,1
2024-10-10T12:28:10.036,0
2024-10-10T12:30:58.128,1
2024-10-10T12:43:02.389,0
2024-10-10T12:46:08.525,1
2024-10-10T12:58:30.455,0
2024-10-10T13:01:18.191,1
2024-10-10T13:13:40.319,0
2024-10-10T13:16:28.119,1
2024-10-10T13:28:53.709,0
2024-10-10T13:31:38.022,1
2024-10-10T13:44:03.618,0
2024-10-10T13:46:47.938,1
2024-10-10T13:59:13.555,0
2024-10-10T14:01:57.836,1
2024-10-10T14:14:27.046,0
2024-10-10T14:17:07.774,1
2024-10-10T14:29:36.976,0
2024-10-10T14:32:17.634,1
2024-10-10T14:44:46.960,0
2024-10-10T14:47:27.692,1
2024-10-10T15:00:00.299,0
2024-10-10T15:02:37.529,1
2024-10-10T15:15:10.187,0
2024-10-10T15:17:47.301,1
2024-10-10T15:30:20.164,0
2024-10-10T15:32:57.239,1
2024-10-10T15:45:33.519,0
2024-10-10T15:48:07.231,1
2024-10-10T16:00:43.437,0
2024-10-10T16:03:17.125,1
2024-10-10T16:15:53.281,0
2024-10-10T16:18:26.949,1
2024-10-10T16:31:03.152,0
2024-10-10T16:33:36.794,1
2024-10-10T16:46:16.635,0
2024-10-10T16:48:46.713,1
2024-10-10T17:01:26.458,0
2024-10-10T17:03:56.587,1
2024-10-10T17:16:36.379,0
2024-10-10T17:19:06.491,1
2024-10-10T17:31:49.853,0
2024-10-10T17:34:16.533,1
2024-10-10T17:47:00.624,0
2024-10-10T17:49:27.332,1
2024-10-10T18:01:53.492,0
2024-10-10T18:04:38.105,1
2024-10-10T18:17:04.306,0
2024-10-10T18:19:48.816,1
2024-10-10T18:32:14.913,0
2024-10-10T18:34:59.314,1
2024-10-10T18:47:25.408,0
2024-10-10T18:50:09.926,1
2024-10-10T19:02:36.064,0
2024-10-10T19:05:20.527,1
2024-10-10T19:17:46.778,0
2024-10-10T19:20:31.117,1
2024-10-10T19:32:57.425,0
2024-10-10T19:35:41.890,1
2024-10-10T19:48:08.126,0
2024-10-10T19:50:52.587,1
2024-10-10T20:03:18.651,0
2024-10-10T20:06:03.100,1
2024-10-10T20:18:29.181,0
2024-10-10T20:21:13.655,1
2024-10-10T20:33:39.744,0
2024-10-10T20:36:24.102,1
2024-10-10T20:48:50.361,0
2024-10-10T20:51:34.834,1
2024-10-10T21:04:01.099,0
2024-10-10T21:06:45.572,1
2024-10-10T21:19:11.883,0
2024-10-10T21:21:56.366,1
2024-10-10T21:34:22.754,0
2024-10-10T21:37:07.249,1
2024-10-10T21:49:37.052,0
2024-10-10T21:52:17.887,1
2024-10-10T22:05:05.471,0
2024-10-10T22:07:28.615,1
2024-10-10T22:20:16.199,0
2024-10-10T22:22:39.113,1
2024-10-10T22:35:26.737,0
2024-10-10T22:37:49.894,1
2024-10-10T22:50:41.102,0
2024-10-10T22:53:00.546,1
2024-10-10T23:05:51.846,0
2024-10-10T23:08:11.494,1
2024-10-10T23:21:02.762,0
2024-10-10T23:23:22.351,1
2024-10-10T23:36:16.430,0
2024-10-10T23:38:32.424,1
2024-10-10T23:51:44.573,0
2024-10-10T23:53:42.354,1
2024-10-11T00:07:15.815,0
2024-10-11T00:08:52.238,1
2024-10-11T00:22:29.381,0
2024-10-11T00:24:02.379,1
2024-10-11T00:37:42.821,0
2024-10-11T00:39:12.310,1
2024-10-11T00:52:56.401,0
2024-10-11T00:54:22.314,1
2024-10-11T01:08:27.648,0
2024-10-11T01:09:32.348,1
2024-10-11T01:23:41.807,0
2024-10-11T01:24:42.453,1
2024-10-11T01:38:36.992,0
2024-10-11T01:39:52.213,1
2024-10-11T01:53:50.574,0
2024-10-11T01:55:02.356,1
2024-10-11T02:09:04.055,0
2024-10-11T02:10:12.290,1
2024-10-11T02:24:17.625,0
2024-10-11T02:25:22.213,1
2024-10-11T02:39:13.457,0
2024-10-11T02:40:32.216,1
2024-10-11T02:54:23.235,0
2024-10-11T02:55:42.097,1
2024-10-11T03:09:36.765,0
2024-10-11T03:10:52.093,1
2024-10-11T03:24:28.915,0
2024-10-11T03:26:01.940,1
2024-10-11T03:39:42.275,0
2024-10-11T03:41:11.661,1
2024-10-11T03:54:52.120,0
2024-10-11T03:56:21.710,1
2024-10-11T04:10:02.123,0
2024-10-11T04:11:31.647,1
2024-10-11T04:25:12.073,0
2024-10-11T04:26:41.579,1
2024-10-11T04:40:25.470,0
2024-10-11T04:41:51.521,1
2024-10-11T04:55:17.975,0
2024-10-11T04:57:01.465,1
2024-10-11T05:10:27.865,0
2024-10-11T05:12:11.551,1
2024-10-11T05:25:37.848,0
2024-10-11T05:27:21.488,1
2024-10-11T05:40:47.817,0
2024-10-11T05:42:31.468,1
2024-10-11T05:55:57.840,0
2024-10-11T05:57:41.494,1
2024-10-11T06:11:07.927,0
2024-10-11T06:12:51.489,1
2024-10-11T06:26:18.006,0
2024-10-11T06:28:01.613,1
2024-10-11T06:41:28.322,0
2024-10-11T06:43:12.031,1
2024-10-11T06:56:39.205,0
2024-10-11T06:58:22.724,1
2024-10-11T07:11:32.198,0
2024-10-11T07:13:33.410,1
2024-10-11T07:26:38.743,0
2024-10-11T07:28:44.140,1
2024-10-11T07:41:49.323,0
2024-10-11T07:43:54.553,1
2024-10-11T07:56:59.953,0
2024-10-11T07:59:05.425,1
2024-10-11T08:12:06.701,0
2024-10-11T08:14:15.638,1
2024-10-11T08:27:34.699,0
2024-10-11T08:29:25.515,1
2024-10-11T08:42:44.805,0
2024-10-11T08:44:35.518,1
2024-10-11T08:58:16.046,0
2024-10-11T08:59:45.550,1
2024-10-11T09:13:25.917,0
2024-10-11T09:14:55.405,1
2024-10-11T09:28:35.822,0
2024-10-11T09:30:05.167,1
2024-10-11T09:43:49.363,0
2024-10-11T09:45:15.312,1
2024-10-11T09:59:17.029,0
2024-10-11T10:00:25.259,1
2024-10-11T10:14:30.559,0
2024-10-11T10:15:35.247,1
2024-10-11T10:29:40.372,0
2024-10-11T10:30:45.095,1
2024-10-11T10:44:36.249,0
2024-10-11T10:45:55.074,1
2024-10-11T11:00:04.422,0
2024-10-11T11:01:05.092,1
2024-10-11T11:15:17.683,0
2024-10-11T11:16:14.896,1
2024-10-11T11:30:31.210,0
2024-10-11T11:31:24.799,1
2024-10-11T11:45:41.078,0
2024-10-11T11:46:34.652,1
2024-10-11T12:00:54.576,0
2024-10-11T12:01:44.636,1
2024-10-11T12:16:07.957,0
2024-10-11T12:16:54.441,1
2024-10-11T12:31:18.181,0
2024-10-11T12:32:04.774,1
2024-10-11T12:46:14.846,0
2024-10-11T12:47:15.584,1
2024-10-11T13:01:07.262,0
2024-10-11T13:02:26.214,1
2024-10-11T13:16:17.766,0
2024-10-11T13:17:36.720,1
2024-10-11T13:31:28.390,0
2024-10-11T13:32:47.314,1
2024-10-11T13:46:38.752,0
2024-10-11T13:47:57.654,1
2024-10-11T14:01:48.813,0
2024-10-11T14:03:07.652,1
2024-10-11T14:16:58.717,0
2024-10-11T14:18:17.571,1
2024-10-11T14:32:08.877,0
2024-10-11T14:33:27.741,1
2024-10-11T14:47:18.806,0
2024-10-11T14:48:37.783,1
2024-10-11T15:02:28.943,0
2024-10-11T15:03:47.795,1
2024-10-11T15:17:38.839,0
2024-10-11T15:18:57.823,1
2024-10-11T15:32:27.744,0
2024-10-11T15:34:07.799,1
2024-10-11T15:47:37.545,0
2024-10-11T15:49:17.747,1
2024-10-11T16:02:47.498,0
2024-10-11T16:04:27.677,1
2024-10-11T16:17:53.905,0
2024-10-11T16:19:37.525,1
2024-10-11T16:33:03.722,0
2024-10-11T16:34:47.330,1
2024-10-11T16:48:13.634,0
2024-10-11T16:49:57.242,1
2024-10-11T17:03:01.787,0
2024-10-11T17:05:07.120,1
2024-10-11T17:18:11.704,0
2024-10-11T17:20:17.042,1
2024-10-11T17:33:18.098,0
2024-10-11T17:35:26.968,1
2024-10-11T17:48:46.059,0
2024-10-11T17:50:36.753,1
2024-10-11T18:03:55.894,0
2024-10-11T18:05:46.647,1
2024-10-11T18:19:02.344,0
2024-10-11T18:20:56.598,1
2024-10-11T18:34:12.285,0
2024-10-11T18:36:06.536,1
2024-10-11T18:49:22.305,0
2024-10-11T18:51:16.529,1
2024-10-11T19:04:28.765,0
2024-10-11T19:06:26.536,1
2024-10-11T19:19:56.437,0
2024-10-11T19:21:36.482,1
2024-10-11T19:35:06.409,0
2024-10-11T19:36:46.479,1
2024-10-11T19:50:16.441,0
2024-10-11T19:51:56.537,1
2024-10-11T20:05:26.368,0
2024-10-11T20:07:06.431,1
2024-10-11T20:20:36.347,0
2024-10-11T20:22:16.424,1
2024-10-11T20:36:03.991,0
2024-10-11T20:37:26.432,1
2024-10-11T20:51:17.401,0
2024-10-11T20:52:36.146,1
2024-10-11T21:06:27.337,0
2024-10-11T21:07:46.217,1
2024-10-11T21:21:37.225,0
2024-10-11T21:22:56.080,1
2024-10-11T21:36:50.624,0
2024-10-11T21:38:06.042,1
2024-10-11T21:52:18.680,0
2024-10-11T21:53:15.787,1
2024-10-11T22:07:31.919,0
2024-10-11T22:08:25.525,1
2024-10-11T22:23:03.073,0
2024-10-11T22:23:35.311,1
2024-10-11T22:37:55.278,0
2024-10-11T22:38:45.314,1
2024-10-11T22:53:26.406,0
2024-10-11T22:53:55.209,1
2024-10-11T23:08:39.908,0
2024-10-11T23:09:05.175,1
2024-10-13T15:18:30.241,0
2024-10-13T15:18:51.884,1
2024-10-13T15:33:09.258,0
2024-10-13T15:34:02.872,1
2024-10-13T15:48:37.825,0
2024-10-13T15:49:13.755,1
2024-10-13T16:04:09.680,0
2024-10-13T16:04:24.448,1
2024-10-13T16:18:09.401,0
2024-10-13T16:19:35.408,1
2024-10-13T16:33:20.193,0
2024-10-13T16:34:46.212,1
2024-10-13T16:47:33.723,0
2024-10-13T16:49:56.884,1
2024-10-13T17:03:34.322,0
2024-10-13T17:05:07.393,1
2024-10-13T17:18:27.391,0
2024-10-13T17:20:18.226,1
2024-10-13T17:33:52.094,0
2024-10-13T17:35:28.739,1
2024-10-13T17:49:42.351,0
2024-10-13T17:50:39.474,1
2024-10-13T18:05:10.648,0
2024-10-13T18:05:49.997,1
2024-10-13T18:20:42.635,0
2024-10-13T18:21:00.809,1
2024-10-13T18:35:56.655,0
2024-10-13T18:36:11.341,1
2024-10-13T18:50:49.598,0
2024-10-13T18:51:21.942,1
2024-10-13T19:06:03.747,0
2024-10-13T19:06:32.578,1
2024-10-13T19:20:20.851,0
2024-10-13T19:21:43.237,1
2024-10-13T19:35:31.606,0
2024-10-13T19:36:54.022,1
2024-10-13T19:50:20.393,0
2024-10-13T19:52:03.997,1
2024-10-13T20:04:50.801,0
2024-10-13T20:07:13.955,1
2024-10-13T20:20:14.930,0
2024-10-13T20:22:23.837,1
2024-10-13T20:35:57.302,0
2024-10-13T20:37:33.890,1
2024-10-13T20:51:24.968,0
2024-10-13T20:52:43.794,1
2024-10-13T21:07:14.139,0
2024-10-13T21:07:53.690,1
2024-10-13T21:22:27.893,0
2024-10-13T21:23:03.667,1
2024-10-13T21:37:56.269,0
2024-10-13T21:38:14.472,1
2024-10-13T21:52:52.993,0
2024-10-13T21:53:25.353,1
2024-10-13T22:08:25.126,0
2024-10-13T22:08:36.279,1
2024-10-13T22:23:39.408,0
2024-10-13T22:23:46.996,1
2024-10-13T22:38:53.191,0
2024-10-13T22:38:57.359,1
2024-10-15T14:53:38.546,0
2024-10-15T14:54:00.277,1
2024-10-15T15:04:28.191,0
2024-10-15T15:09:10.224,1
2024-10-15T15:23:08.368,0
2024-10-15T15:24:20.142,1

[lutz85]

IDEA: making the query over a larger timeframe (to avoid the issues with ffill of NaNs and also to avoid completely empty data). and then using whatever grafana has to offer to trim it down to the current timeFilter

[orosolido]

Just thinking out of the box…
Why don’t you (force) fill all hits that are NaN with a specific custom value like e.g “1.9991” or “0.0000001”

Then there are 2 options:

1. consider this specific value as == no change
2. filter this specif value out and you will only keep dates when there is an actual change

[yosiasz]

@orosolido

NaN with a specific custom value like e.g “1.9991” or “0.0000001”

would that skew the mean(value) ? not even sure influxql has that capability

[lutz85]

yes it would skew it. probably i’d be able to filter those values out.

But my big problem are not the NaN values: my big problem is when I have no data / empty query result, in which case i just can’t get it to work to show the last actual value in the gauge that is the query result of prev / prevval query as defined above.

[orosolido]

With option 2, i dont think it will affect the MEAN value, because they are not part of the calculation.

With option 1, I suppose you can do/apply a ‘correction’, because you can keep count how many fields are “NaN”

[orosolido]

@lutz85

yes it would skew it. probably i’d be able to filter those values out.

But my big problem are not the NaN values: my big problem is when I have no data / empty query result, in which case i just can’t get it to work to show the last actual value in the gauge that is the query result of prev / prevval query as defined above.

That’s why i mentioned thinking out of the box.
There are more ways to achieve the same goal

[orosolido]

@yosiasz

would that skew the mean(value) ? not even sure influxql has that capability

Indeed that is a good question!

I know influxQL has restrictions compared to influxDb.
Unf. im not able to verify if this is possible my idea is for now only theoretically.
I know its possible in influxDb with command |> fill(value: 0)

[lutz85]

you mean writing more (frequent) data to the DB? Yes in theory I can set the KNX system up to write 1 value every 10 minutes or so. But imagine for a whole week this means like 1000 datapoints where actually ONE dp would be enough (only writing OnChange values to DB like now)… but yes, this would be 1 way to solve it - it’s rather bruteforcing imho.

I’d rather find a way to handle those “temporal gaps” gracefully with grafana if possible at all

[orosolido]

i never stated it was the most efficient approach

But i understand that would be the way to go.
My intention was more to give you a new insight, that might help to the end solution

[yosiasz]

I cant seem to be able to import this csv in my docker influxdb v1. @lutz85 how do you import data. i have done it before but has been a while since I dealt with v1. In fact we full abandoned influx(xyz)

the way I see it, the solution might be outside of using influxql/grafana mean etc to gracefully handle “temporal gaps”

[lutz85]

Good question! I just exported this using influx v1 chronograf. NodeRed on my Home Assistant is ingesting all the data from the knx system to the influxDB.

I think I found a solution now, following my idea from above post.

Something like this will solve both my problems:

• fill the NaNs at beginning of $timeFilter window properly
• avoid an empty query result

Query structure:

• inner select statement that collects data for a larger timeperiod and forwardfills it
• outer select statement that will filter it back down to the $timefilter period

I am still adjusting it a bit to make it more robust and get my final gauge reading output, but I think this is the way to go.

select * from
(
SELECT last(“value”)
FROM “autogen”.“Wohnzimmer_Heiz_Status_Stellgroesse_alle_3”
WHERE time > now()-30d
GROUP BY time($__interval) fill(previous)
)
WHERE $timeFilter