Dashboard Math Problems

[corincorvus]

Hello,

I’m currently trying to display my energy consumption in days on the Grafana dashboard.

I have a sensor that measures my total consumption in kWh. This data comes from Node-red via MQTT.

When I create a Time Series on the dashboard and set the query to 1 day using “distance” and “difference,” I get completely incorrect numbers.

For example, today:
I have a balcony solar power system, and it generates a lot of energy. I know this because I have a plug in between that measures and documents it. According to my calculations, I haven’t consumed more than 2 kWh today. However, when I calculate the daily value through Grafana, it shows almost 5 kWh. Why does this happen?

The sensor measuring the electricity is directly connected to the phase and is very reliable.

How can I set this up correctly? It should show from 00:00 to 23:59, and if it lacks data from the current day, that’s okay.

Here you can see, the total Measurement Sensor is unter 2 kwh today.

its the same measurement

Thanks

[grant2]

Welcome @corincorvus to the Grafana forum.

I do not have much experience with InfluxQL nor with the DISTINCT() function that you are using, but my guess is that that is where the problem is happening. Can you explain why you are using the DISTINCT function?

Are you sure it should not be nested, such as:

SELECT DIFFERENCE(DISTINCT( [ <field_key> | /<regular_expression>/ ] ))

[corincorvus]

Hi,

i only was looking for a solution, so i found some threads, where distinct was used.

My problem is, at the moment the Graph use 24h as 1d. If we play with our pcs at night the Day before, the kwh are measured in this time is added on the daily graph. But i need a Graph, who only show me today kwh between 00:00 and 23:59.

A Graph for Today energy use, it show me on 7 o clock after wake up 5 kwh and reduce the kwh over the day unter 3 for example, but in truth we only used 1kwh between 0:00 and 7:00 is not useful.

If you have another Solution for this with 1 Measurement i can test it.

Thanks

[corincorvus]

Nobody?

i am looking for in best case a simple solution.

Thanks

[grant2]

This will show the current day so far:

If you wanted to filter out the hours from 00:00 to 06:59, then the only way I know how to do this is using Flux with the hourSelection function. That means you would have to switch from InfluxQL to Flux.

[corincorvus]

hm. in my case it wont do that.

it shows me the value of this year.

[grant2]

If you are seeing the YTD value of an always-increasing series, then it’s probably because you are viewing the “Last” value instead of the delta (what some call “spread”).

Try changing from Last to Delta or Difference:

[corincorvus]

Delta show me a very crazy value

Difference looks correct. i will check it after work. (6,17kwh at the moment)

Can i build a bar chart for every day? with distinct and difference i dont get good values.

thanks in forward.

[grant2]

@corincorvus

Can i build a bar chart for every day?

Something like this?

[corincorvus]

exactly!

[grant2]

To create the above, use a Bar Chart visualization and use an override to get the month and day (and thus not display the hour, since that is not relevant).

In your InfluxQL query, use the pencil icon to view the raw query and make sure the last part is:

AND $timeFilter GROUP BY time(1d)

Result should look like this:

[corincorvus]

@grant2

AND $timeFilter GROUP BY time(1d)

Hey,

i struggle with:

SELECT mean(“kwhtotalsolar2023”) FROM “strom” WHERE AND $timeFilter GROUP BY time(1d)

The Original at the moment is:

SELECT mean(“kwhtotalsolar2023”) FROM “strom” WHERE $timeFilter GROUP BY time(1d) fill(null) tz(‘Europe/Berlin’)

[yosiasz]

look at your WHERE clause WHERE AND

SELECT mean("kwhtotalsolar2023") 
  FROM "strom" 
WHERE AND $timeFilter GROUP BY time(1d)

[corincorvus]

i have no idea, what i have to do with the snippet.

[grant2]

Is the graph wrong?

[corincorvus]

Yes. i dont use 1,75mwh per day

[grant2]

Can you change your query to start with this and then see how the values in the graph look?

SELECT last("kwhtotalsolar2023") FROM...

[corincorvus]

no data with and without the “and” snippet above.

[grant2]

It looks like you just copied and pasted my text. That was just to show you how to start the query. The full query would be:

SELECT last("kwhtotalsolar2023") 
  FROM "strom" 
WHERE AND $timeFilter GROUP BY time(1d)

[corincorvus]

I did it:

@grant2 i get a data error, if i add a AND after WHERE.

No i only need to make visible the Values. On Bar Chart i can see the Values, but dont stack the 2 queries. On Time Series i can Stack (screenshor above), but not make visible the Values. Can i solve it?