So I have a table that looks like this. What I want is to another column based on the last two column of my table with a formula of latestcolumn(column3) / previouscolumn(column2).
my problem is the number of columns is dynamic which mean I can have a up to 8 columns.
title column1 column2 column3
A 1 2 3
B 4 5 6
C 7 8 9
Here is the search i used to get my initial table
index=main
| xyseries title column count
Maybe, this way will suit you.
index=main | xyseries title, column, count | join title [ search index=main | eventstats values(column) as vals, dc(column) as colcount | eval last=mvindex(vals, colcount-1), prev=mvindex(vals, colcount-2) | where column=last OR column=prev | eval column=if(column=last, "last", "prev") | xyseries title, column, count | eval result=prev/last | fields - last, prev]
Though, I believe it's possible to implement it easier.
Maybe, this way will suit you.
index=main | xyseries title, column, count | join title [ search index=main | eventstats values(column) as vals, dc(column) as colcount | eval last=mvindex(vals, colcount-1), prev=mvindex(vals, colcount-2) | where column=last OR column=prev | eval column=if(column=last, "last", "prev") | xyseries title, column, count | eval result=prev/last | fields - last, prev]
Though, I believe it's possible to implement it easier.
You can try something like this
index=main
| xyseries title column count | eval column4=round(column3/column2,2)
Let me know if this helps!
as I've said the number of columns is not fixed, so there can be columns 1,2,3,4,5