I managed to create two boundary lines (UCL and LCL) which was my original goal, but I suspect that I did this in a very clumsy way by creating 4 separate queries. Can anyone offer a more efficient way to do this? My eventual goal is to do comparisons (e.g. is Setpoint > UCL? is Setpoint < LCL?).
Otherwise using your approach you should execute the query only once, put the result in a variable (data in the example) to avoid to execute multiple queries for the same data:
data = from(bucket: "bucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "measurement")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
data
|> filter(fn: (r) => r["_field"] == "setpointValue")
|> yield(name: "SETPOINT")
data
|> filter(fn: (r) => r["_field"] == "actualValue")
|> yield(name: "ACTUAL")
data
|> filter(fn: (r) => r["_field"] == "setpointValue")
|> map(fn: (r) => ({ r with _value: r._value +1.5 }))
|> yield(name: "UCL")
data
|> filter(fn: (r) => r["_field"] == "setpointValue")
|> map(fn: (r) => ({ r with _value: r._value -1.5 }))
|> yield(name: "LCL")
Domenico: The first query you wrote does indeed produce 1 single table with everything that is needed. My original question was how to produce lines (at 3.5 and 6.5), but all this table shows is a single line.
For the other query that you wrote (which uses the data variable), thank you for explaining that approach. It makes sense to me, but for some reason I am getting the error shown below. I think it has to do with the mean function in line 4?
Hi @grant1, the pivot with a single table output is not for the visualization in the dashboard but for the analytics, e.g. if you need to export the table as a python dataframe.
For the failed aggregation in the second approach, you probably have some other field containing strings and the mean aggregation function fails to aggregate all data.
In this case you must filter only for your required numeric fields: setpointValue and actualValue before aggregate, please try the following code:
data = from(bucket: "bucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "measurement")
|> filter(fn: (r) => r["_field"] == "setpointValue" or r["_field"] == "actualValue")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
data
|> filter(fn: (r) => r["_field"] == "setpointValue")
|> yield(name: "SETPOINT")
data
|> filter(fn: (r) => r["_field"] == "actualValue")
|> yield(name: "ACTUAL")
data
|> filter(fn: (r) => r["_field"] == "setpointValue")
|> map(fn: (r) => ({ r with _value: r._value +1.5 }))
|> yield(name: "UCL")
data
|> filter(fn: (r) => r["_field"] == "setpointValue")
|> map(fn: (r) => ({ r with _value: r._value -1.5 }))
|> yield(name: "LCL")
Just one question (even though the graph looks perfect)…
When we put yield(name: “UCL”) in the query, that creates a column called UCL. Shouldn’t the UCL calculated value (5 + 1.5 = 7.5) appear in this column of the data?
Well, no, UCL it’s just the name of the output table, the column_result is not populated because with that code you are just replacing the values (in the column _value) of the field setpointValue with its value + or - 1.5.
We use this approach because the embedded dashboard will plot the just the _value column by default for each table (not other named columns), and this is why the pivot code I provided produces a perfect table with all named column but it’s not plotted in the chart.
