Spread() but only with increasing value differences

[osulivande]

Hi, i am quite new to grafana. I am monitoring energy consumption of some of my electrical devices and display them grouped by day like this:

Today was the first day one of my energy meter devices kind of reset its power calculation from 14kWh to 0kWh. You can see this in the last huge bar.

This is a problem for me because i calculate the energy consumption based on the “spread” function

SELECT spread("value") FROM "kWh" WHERE ("entity_id" = 'wohnzimmer_tv_energy') AND $timeFilter GROUP BY time(1d) fill(null)

Is there something to either exclude huge spikes or only calculate increasing spread values?

Thanks a lot.

[michaeluray]

Did you actually find a solution for this?
I have tried it with NON_NEGATIVE_DERIVATIVE but not with success.

[grant2]

@michaeluray Welcome to the Grafana forum.

Do you have the ability to change from InfluxQL to Flux? This sort of thing is much easier with Flux. If you post a sample of your data, me or someone can probably work out the Flux query to try out some suggested solutions.

If you are on the fence, ask @monstereye how his switch to Flux went.

[michaeluray]

I have an energy counter from a Shelly Plug S which increases all the time, but it starts from 0 after a power fail.

I group the spread() by time (for example 24h) to get the consumption per day.

SELECT spread("value") FROM "mqtt-client.0.shellies.Mikrowelle.relay.0.energy" WHERE $timeFilter GROUP BY time(1d) fill(null)

So I just want to ignore that one data record where it starts over from 0.
I actually would prefer to stay with InfluxDB 1.8 to have the convinent graphical settings possibility in Grafana, which I would miss with InfluxDB 2.x.

[jangaraj]

What is a problem with NON_NEGATIVE_DERIVATIVE? I guess you need to use subqueries (so no graphical interface, but pure InfluxQL).

My guess:
1.) inner query NON_NEGATIVE_DERIVATIVE with full granularity → so you will have rate of change
2.) outer query SUM + some time multiplication of results from inner query with daily aggregation

[michaeluray]

Thanks for the hint with the sum, the following query solved it for me:

SELECT spread("ccs") FROM
(SELECT 
	CUMULATIVE_SUM(SUM(nnd)) as ccs
FROM (
	SELECT NON_NEGATIVE_DIFFERENCE("value") AS nnd FROM "mqtt-client.0.shellies.Mikrowelle.relay.0.energy"
	WHERE $timeFilter
) GROUP BY time(1s)) GROUP BY time(1d)

[michaeluray]

@grant2

Do you have the ability to change from InfluxQL to Flux? This sort of thing is much easier with Flux.

I actually have InfluxDB 2.x running in parallel for recording, but I never used it so far to read out data from it.
How would a Flux query look like to fix such an issue with reseting counters?

[frk]

Thank you for this working query I would never have been able to set myself.
@All : please note that this query will give a cumulative graph starting from 0 on the specified
$timeFilter timespan, even using a permanent counter (i.e in my case, a total working hours counter on my projector).