Calculating duration of boolean events using Flux

Fluxlang
1

Happy New Year to everyone.

I have a Flux query where I am monitoring the feedrate of a machine. If the feedrate is >= 1, then it’s true, and if not, it’s false. Here is the query and a snippet of the ~14,000 record output:

from(bucket: "AMPdata")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "SingleRotaryFurnaceElementData")
  |> filter(fn: (r) => r["EquipNumber"] == "8")
  |> filter(fn: (r) => r["MeasType"] == "actual")
  |> filter(fn: (r) => r["_field"] == "Feedrate")
  |> map(fn: (r) => ({ r with _value: if r._value >= 1 then
                "true"
            else
                "false",
        }),
    )
  |> yield(name: "Running or Not Running")

image
image1770×313 46.4 KB

So far, so good. Next, I put these 3 lines just before the yield() function and I get the total number of true and false records.

  |> stateDuration(fn: (r) => r._value == "true")
  |> group(columns: ["_value"])
  |> count(column: "stateDuration")

image

Note that in the above stateDuration predicate function, it does not matter if I use r._value == “true” or r._value == “false”. The resulting output is the same.

And this is where I am getting stuck. What I would like to do is have a single table showing 3 values:

total number of true readings
total number of false readings
total number of true and false readings (i.e. total number or records)

I tried various techniques recommended in this thread, but no dice.

A second issue is that I want to do some basic math on the above output (multiply 1998 by 0.05). When I use a simple map function like this:

|> map(fn: (r) => ({r with NewValue: r.stateDuration * 0.05}))

I get this error:

map: type conflict: float != int

Ultimately, I am trying to calculate the % of time the feedrate is >= 1. Maybe my above functions are not the right approach.

Any help is much appreciated.

2

I’m in the process of self education to learn flux.

Try this

instead of multiply a float with integer try multiply by 5 and divide 100 (integers)
//Count true and false
z = from(bucket: “Boiler”)
|> range(start: -8h)
|> filter(fn: (r) => r[“_measurement”] == “Ems”)
|> filter(fn: (r) => r[“_field”] == “heatingactive”)
|> filter(fn: (r) => r[“tag”] == “Nefit”)
|> map(fn: (r) => ({ r with _value: if r._value == true then
“true”
else
“false”,
}),
)
|> stateDuration(fn: (r) => r._value == “true”)
|> group(columns: [“_value”])
|> count(column: “stateDuration”)
|> map(fn: (r) => ({r with NewValue: r.stateDuration * 5/100 }))
|> yield(name: “Running or Not Running”)

//aditional query
//Calculate sum
y = from(bucket: “Boiler”)
|> range(start: -8h)
|> filter(fn: (r) => r[“_measurement”] == “Ems”)
|> filter(fn: (r) => r[“_field”] == “heatingactive”)
|> filter(fn: (r) => r[“tag”] == “Nefit”)
|> map(fn: (r) => ({ r with _value: if r._value == true then
“true”
else
“false”,
}),
)
|> stateDuration(fn: (r) => r._value == “true”)
|> count(column: “stateDuration”)
|> map(fn: (r) => ({r with NewValue: r.stateDuration * 41/1000 }))
|> yield(name: “sum”)

1 Like
3

Thank you @Jarifed

Multiplying by 5 and dividing by 100 (instead of multiplying by 0.05) did the trick:

image
image837×177 8.26 KB

Given the output above, what do I need to do to get the % amounts, e.g.

false: 44 / (44+52)
true: 52 / (44+52)

1 Like
4

Can anyone offer up a hint as to how to get the % amounts from the above two tables?

5

@grant1
I spend some considerable time but I was not able too.
but below I got this result

Blockquote
x =from(bucket: “boiler”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “Ems”)
|> filter(fn: (r) => r[“_field”] == “heatingactive”)
|> filter(fn: (r) => r[“tag”] == “Nefit”)
|> map(fn: (r) => ({ r with p_true: if r._value == true then
1
else
0,
}),
)
|> sum(column: “p_true”)
|> keep(columns: [“p_true”, “tag”])

y =from(bucket: “boiler”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “Ems”)
|> filter(fn: (r) => r[“_field”] == “heatingactive”)
|> filter(fn: (r) => r[“tag”] == “Nefit”)
|> map(fn: (r) => ({ r with p_false: if r._value == false then
1
else
0,
}),
)
|> sum(column: “p_false”)
|> keep(columns: [“p_false”, “tag”])

result = join(tables: {t1: x, t2: y}, on: ["tag"])

|> map(fn: (r) => ({r with p_true_false: float(v:r.p_true) / (float(v:r.p_true)+ float(v:r.p_false) ) }))
|> map(fn: (r) => ({r with p_false_true: float(v:r.p_false) / (float(v:r.p_true) + float(v:r.p_false))  }))

|> yield(name: “percent”)

Blockquote

6

Thanks @Jarifed

I tried many, MANY combinations of different ideas (joins, unions, pivots, etc.)…so many that it has all become a blur. BUT, in the end, pieces of your scripts helped me figure it out. Here is the final query. I am pretty sure there is a more efficient way to calculate the percent “on” (or true") for a machine, but this works for now.

t1 = from(bucket: "AMPdata")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "SingleRotaryFurnaceElementData")
  |> filter(fn: (r) => r["EquipNumber"] == "8")
  |> filter(fn: (r) => r["MeasType"] == "actual")
  |> filter(fn: (r) => r["_field"] == "Feedrate")
  |> map(fn: (r) => ({ r with _value: if r._value >= 1 then
                "true"
            else
                "false",
        }),
    )
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> filter(fn: (r) => r["Feedrate"] == "false")
  |> count(column:"Feedrate")

t2 = from(bucket: "AMPdata")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "SingleRotaryFurnaceElementData")
  |> filter(fn: (r) => r["EquipNumber"] == "8")
  |> filter(fn: (r) => r["MeasType"] == "actual")
  |> filter(fn: (r) => r["_field"] == "Feedrate")
  |> map(fn: (r) => ({ r with _value: if r._value >= 1 then
                "true"
            else
                "false",
        }),
    )
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> filter(fn: (r) => r["Feedrate"] == "true")
  |> count(column:"Feedrate")

result = join(tables: {t1: t1, t2: t2}, on: ["EquipNumber"])
 |> map(fn: (r) => ({r with percent_true: float(v:r.Feedrate_t2) / (float(v:r.Feedrate_t2)+ float(v:r.Feedrate_t1) ) }))
 |> keep(columns: ["percent_true"])
 |> yield(name: "percent_running")
1 Like