Transform - remove values below 0/clamp above 0

Grafana Time Series Panel
,
1

On Grafana 10.0.1, I have the following time series visualization generated from influxb data:
grafik
The green line is the result of a query, the red line is the result of calculation reduce-row transform that is the total of some other queries, and the blue line is a calculation binary operation transform that is the difference between the two.

What I’m trying to achieve here is to clamp the blue line to values >= 0, so basically like this:
grafik

I tried using a “Filter data by Values” transform, but that is really ugly, because it doesn’t clamp the data, it deletes it, so fill rules get applied and it looks like you don’t have any data there, and also: It delete the data from ALL lines, not just the blue line:

grafik
grafik697×365 17.8 KB

Is there a way to replace all values of the blue line that are below 0 with 0, without affecting the other lines?

2

Hi @simonvanbernem

I played with some test data and confirm what you wrote, i.e. using the transformation “Filter Data by Values” does not really help you accomplish what you want.

Thinking about it some more, I think that your red & blue lines need to come from InfluxDB, i.e. you need to do the calculation there with an IF < 0, then 0 clause. You did not mention if you are using Flux or InfluxQL, but I believe that using a conditional function to transform a value can only be done using a Flux map() function. Here is one example I found on the InfluxDB forum:

|> map(fn: (r) => ({
        r with heatIndex:
            if  (_steadman(t: r.temperature, h: r.humidity) + r.temperature)/2.0 < 80.0  then _steadman(t: r.temperature, h: r.humidity)
            else if ( r.humidty < 13.0 and r.temperature > 80.0) then (_rothfusz(t: r.temperature, h: r.humidity) - (((13.0-r.humidity)/4.0)*math.sqrt(x: ((17.0-math.abs(x: (r.temperature-95.0))/17.0)))))
            else if r.humidity > 85.0 and r.temperature >= 80.0 and r.temperature <= 87.0 then (_rothfusz(t: r.temperature, h: r.humidity) + (( r.humidity-85.0 )/10.0) *((87.0-r.temperature)/5.0))
            else (_rothfusz(t: r.temperature, h: r.humidity))
    }))
3

Thanks for your answer! I used the visual editor to create these queries (example):

grafik
grafik918×271 23.8 KB

Does that tell you if I’m using Flux or InfluxQL? I’m quite new to this, so I don’t know where to look up what query language I’m using or how to change it. The query looks like a different syntax to yours in raw query mode:

grafik
grafik930×178 16.1 KB

4

You are using InfluxQL.

How far along is your database? Do you have months or years of data already stored?

Before you consider changing to Flux, you should look at the latest InfluxDB product (“IOx”, or Influx 3) that uses SQL and check if conditional functions are supported (their forum and Slack channels are pretty helpful).

5

I only have a weeks worth of data that won’t hurt too much if lost. I’ll look into it. Thanks!