Custom Math Formular Transformation?

Grafana Dashboards
1

  • What Grafana version and what operating system are you using?
    Windows Server 2016, Grafana 10.2.2

  • What are you trying to achieve?
    I have a multiframe time series, like this:

11:00   TagA    5
11:00   TagB    7
11:00   TagC    20
12:00   TagA    6
12:00   TagB    8
12:00   TagC    22

I need a calculated Value with the Formular:

TagC - (TagB + TagA)

Can I achieve this with Grafana Transformations?

So far I applied the “Prepare Time Series” → “Multi-Frame” Transformation so I have the Tags Split in Data Frames, but now I am a little stuck with my math formular.

I can apply the Transformation “Add field from calculation” → “Binary Operation” and then I can make a calculation with only two fields e.g. TagB + TagA, but it seems I cannot write a custom math formular?

I achieved my result by adding two “Add field from calculation” Transformations,
First I calculate

PartValue = (TagB + TagA)

And a second one afterwards

FinalValue = TagC - PartValue

This is quite cumbersome especially with huge formulars, is there any other way?
I cannot really change the data I get from the datasource, it is a SQL-Server like system, we need to achieve it within Grafana

br
Stefan

2

What is your datasource?

3

Aveva Historian. It is a proprietary database that can be accessed through specific SQL-Server SQL queries.

4

Can it do pivots, then do the math

5

Grouping to matrix do the trick.

image
image1178×804 51.9 KB

Jo

1 Like
6

That is exactly the workaround I described above, but I use the “Prepare Time Series” instead of “Grouping to matrix” as the first one, I need to check the difference of those two.

However if I have a very long formular I end up with so many “Add field from calculation” transformations that it gets quite confusing for the users.

Is there no better transformation available for custom calculations?

7

I think there isn’t one.
with tranformations you have to do it one after an other.
These kind of mathematical transormation ist missing.
Or you have to handle it in the query.

Jo

8

Doable via pivot if that query language can do pivot

You end up with a table as follows

Time,TagA,TagB TagC
11:00,5,7,26

Then you inplement the math on it aince now the data points are horizontal
0 transformation

9

That is working yes, however I was hoping for a Transformation approach in the Grafana UI.

Some of our users have limited SQL knowledge so I try to propose solutions through the Grafana UI instead of putting the logic in the SQL Queries.

1 Like
10

so @joachimschiewek proposed transformation, I proposed pivot. Not sure what more we can do to help?

Anyways why are users carving up sql queries in grafana?

11

Maybe I am not understanding correctly.
From your answer „Then you inplement the math on it“ → what do you mean exactly? How do I implement the math?

I was looking for a way todo such calculations with a Grafana transformation.

This is possible as mentioned already in my inital post by stacking several binary operations and also mention by @joachimschiewek. However it is cumbersome stacking so many binary operations if the mathematical formular contains lots of parameters.

From my understanding the solution is to stack several binary operations or do it directly in the query with SQL.

1 Like
12

Well since you said "Some of our users have limited SQL knowledge " not sure if it will help to propose a solution with SQL