Track the sum of Values only when the value goes down

Fluxlang
1

I have a tank that I can measure the total change in from the beginning of the day until the current time.
This works great to find out how much I have used in the day when there is no filling up of the tank.
But when I fill up my water tank this “current days usage” goes way out as the difference between the first and the last value messes up that logic.

I am trying to find the total amount that goes out of the tank in the current day even when there is a day that fills up the tank.

I have found the function “increase()” that I thought could work in conjunction with other logic but it turns out this is accumulative and will give incorrect readings if the value goes up and down with sensor fluctuations over time.

Does anyone know of a way that could help me get the total drop in the tank even when the tank has been filled up during that day ? ( only negative movements in the tank )

I am playing around with some different Flux commands but so far no luck, I really think this could be a general user need so I think I am missing something that I just don’t have the experience for.

Thanks for any input.

2

I have tried this Flux function that I made, so I have no doubt there are a fair amount of errors.

from(bucket: "watertank")
  |> range(start: today())
  |> filter(fn: (r) =>
    if max(column: "Volume") > (500 + first(column: "Volume") ) 
      then (max(column: "Volume") - min(column: "Volume")) - ( (first(column: "Volume") - last(column: "Volume") )* -1 )
    else ( first(column: "Volume") - last(column: "Volume") )
  )

My logic is as follows :
If the max value today is greater than 500L ADDED to the first value of the day, then there was a fill up today.
If there is a fill up do this, (max - min) subtract from ( (first value - last value) * -1 )

Else just output the first and last value difference ( first() - last() )

3

Hm, I can think of three ways in which the arithmetic doesn’t work for me.

  1. “If the max value today is greater than 500L ADDED to the first value of the
    day, then there was a fill up today.”

That requires that less than 500l was used before the fill-up occurred. I
don’t know what size the tank is, but suppose it’s 1000l and it was 800l full
at the start of the day. Someone uses 600l (level=200l) and tops it up with
500l (level=700l), then uses another 300l by the end of the day.

Max value=800l. Start value=800l. Max is not greater than start+500l, top-up
gets overlooked.

  1. (Also assuming a 1000l tank). Start with 300l in the tank, top up
    immediately with 500l (level=800l). Then use 600l (level=200l).

Then (max-min) = 600l, (first-last)*-1 = -100l, subtract to get 700l (which is
not the amount used).

  1. If it’s at all possible to use enough to require more than one top-up in a
    day, you have no idea how many times it happened. Start with 800l, use 700l,
    top-up with 500l, use 500l, top-up again with 500l. You can’t detect the 500l
    used or the second top-up.

Finally, why do (first - last) * -1 instead of just (last - first)?

Antony.

4

Hi, those are really good points.
Thanks.

As far as I am aware there is no chance of having the need to fill up more than once a day in my situation, I simply will never use that much. ( unless I get a leak or something I suppose )
There will always be more than 500L left in the tank if I fill up as generally I get about 2 weeks out of my tank. ( again, as far as I have gone so far )
But I would love to know a way of making this work if you have any ideas.

I completely overlooked just subtracting the first and last values the other way around, great idea thank you.

Would you be able to let me know if my attempt at Flux is on the right path ?
I am reading the docs but have not got much luck as of yet.

Thank again.

5

As far as I am aware there is no chance of having the need to fill up more
than once a day in my situation.

Okay, that solves one problem :slight_smile:

I completely overlooked just subtracting the first and last values the
other way around, great idea thank you.

It’s just arithmetic :slight_smile:

Would you be able to let me know if my attempt at Flux is on the right path?

Sorry, I use InfluxQL - I don’t know Flux yet myself :slight_smile:

However, as a general comment, your challenge is quite a difficult one
(certainly far more difficult than it appears at first glance).

If you want to measure liquid consumed from the tank, you should ideally be
measuring flow out of the tank. However, I appreciate that changing sensors is
either expensive, inconvenient or simply not an option for you.

I really only think you can achieve what you need by having some query which
can give you the difference (delta) between successive measurements - is there
anything in the data path between sensor and Influx which could do this for
you?

If the (previous value - current value) is non-negative, then you have a data
point for the amount consumed in the sampling interval. If it’s negative,
then you know the tank got filled up, and you can simply throw away the number,
and possibly have a very small discrepancy in your usage figures because you
can’t tell if any got used at the same time as filling up.

But, I think doing any sort of analysis over an entire day’s values will fall
into one of the problems I outlined eariler. I think you have to be able to
get a sequence of non-negative deltas to show the values you want.

Antony.

6

Maybe I don’t understand the problem, but can you do something like this?

yourdata
|> difference()
|> filter(fn: (r) => value: r._value < 0)
|> cumulativeSum()

You take the difference of the successive values and total the decrease in value. You still need to deal with a negative result, but that’s kind of technicality. You can also do this in one step using the reduce function.

7

Hi.

This works pretty well actually, at least close enough that I don’t feel the need to struggle to improve it a hell of a lot.

The inaccuracies that I get now using this happen due to my data fluctuating a little when the sensor readings go up and down due to its own temperture drift I think.

I have used your code pretty much as is except for one change.
( I removed the “value:” from your filter line, I don’t know why but grafana didnt like it being there, I don’t know enough to say why but I presume syntax or something )

Thank you so much, this is exactly what I needed.

My only problem with this is turning the number into a positive so it looks “nicer”
And this is how you do that :

map(fn: (r) => ({ Volume: r._value*-1.0}))

It must be a float (-1.0) because the types need to be the same.

Thanks again.