How to calculate with multiple values in a table?

[dailv1808]

I have few results which look like below in a table:

ID         Ask        Bid
1   |      4     |    3
2   |      5     |    6
3   |      7     |    8

I want to create new field with newfield=(4*6-8)/8
So how do I it?
All suggestions are welcome.

[Sukisen1981 · Accepted solution]

| sort ID
| streamstats count as row
| eval x= If(row=2,Bid,0)
| eval y= If(row=3,Bid,0)
| eval z= If(row=1,Ask,0)
| eval t=x+y+z
| streamstats current=false last(t) as prevt
| eval z1=case(row=2,t*prevt)
| streamstats current=false last(z1) as prevz1
| eval final=(prevz1-t)/t
| eventstats max(final) as net
| eval net=if(row=1,net,0)
| table ID,Ask,Bid,net

If you want to display net in ALL rows remove the final eval statement

[Sukisen1981]

see my reference screen shot below & please run the new table command as state above.

[dailv1808]

Please check out my answer above

[dailv1808]

I tried but it does not seem to work properly
You can see photo below
![alt text][1]

https://imgur.com/a/ZKdvP

[Sukisen1981]

I think what you expect and what you have descried is perhaps having gaps.

Are you getting net as 2?
sorry your image is not opening up

[dailv1808]

sorry, because my english is not good.
You can see the result of you search command in the image above by link https://imgur.com/a/ZKdvP.
My expectation is at the net field displaying the result of the operation (4 * 6-8)/8
look like
ID Ask Bid Net
1 | 4 | 3 | 2 << --- resuilt of (4 * 6-8) / 8
2 | 5 | 6 |
3 | 7 | 8 |

[Sukisen1981]

can you please replace the last table command with additional fields?
| table ID,Ask,Bid,t,prevt,z1,prevz1,final,net

[Sukisen1981]

ha I think I figured out your issue... your row numbers....are they really 1,2 and 3?, can you add something like this and pass the results back?

| table ID,Ask,Bid,t,prevt,z1,prevz1,final,net,row

[dailv1808]

when i change to "table ID,Ask,Bid,t,prevt,z1,prevz1,final,net,row" then the row field show number 1,2 ,3. Other field t,prevt,z1,prevz1,final,net are not show enything.

[Sukisen1981]

hmmm your fields - results.askprice etc are they in number format? I know it displays as number but sometimes it could be a string having numbers as characters.
Also, what is the source of your data, CSV or some log files?

[dailv1808]

i'm sure they are in number format. Because the source of data from Json log file.

{ [-]
   message:
   request: /api/v1/getmarket
   result: { [-]
     AskPrice: 0.07598950
     BTCVolume: 29.08189407
     BidPrice: 0.07590000
     BuyOrderCount: 102
     Change: -1.68
     HighPrice: 0.07998059
     LastPrice: 0.07590000
     LowPrice: 0.07200000
     MarketID: 87
     SellOrderCount: 103
     TradeCount: 516
     Volume: 29.08189407
   }
   success: 1
}

[dailv1808]

I read your search command, but i cann't understand your idea. Could you plz explain them ?
I thing with other program languages or in exel, this issue is so easy. But In Splunk, it is so hard for me 😞

[Sukisen1981]

Hi the fields as it is coming in statistics tab , maybe that is causing the issue...can you try this and replace the ID, ask, bid fields with the correct field names?

| sort ID
|eval results.AskPrice=tonumber(results.AskPrice)
| eval results.BidPrice = tonumber(results.BidPrice)...
rest of the query as in your screen shot

[Sukisen1981]

basically just use 2 evals to convert the ask and bid price to numbers...it could be the only issue that I can see, I tied the query and it works with my test data.

[dailv1808]

could you help me rewrite your search command with this condition net=(t1*t3-t2)/t3
see my screenshot: https://imgur.com/a/9kNuX

[Sukisen1981]

thats easy but i am worried about something else, you do realize that this query works ONLY for 3 rows of data? So, what happens when you have 9 or 12 rows of data? should it not be like each 3 rows should return 3 different values? Please answer so that I can club the query you asked above with the solution for multiple sets of 3 queries...something like below
row net
1 net=(t1*t3-t2)/t3
2
3
4 net=(t4*t6-t5)/t6
5
6
... and so on

[dailv1808]

Yes. Actually, my data has lots of rows. But the first I need to understand the algorithm with 3 rows.
Would you please explain the search statement below to me?
....
| eval t = x + y + z
| streamstats current = false last (t) as prevt
| eval z1 = case (x! = 0, t * prevt)
| streamstats current = false last (z1) as prevz1
| eval final = (prevz1-t) / t
| eval net = round (case ((row% 3) = 0, final), 2)
.....

[dailv1808]

Ok. thank you, I renamed results.AskPrice, results.BidPrice fields then convert tonumber, and it work. thank you so much again!

[Sukisen1981]

try this -

sourcetype="ask"
| sort ID
| streamstats count as row
| eval p=row%3
| eval x= If(((p+1)%3=0),Bid,0)
| eval y= If((row%3)=0,Bid,0)
| eval z= If(x=0 AND y=0,Ask,0)
| eval t=x+y+z
| streamstats current=false last(t) as prevt
| eval z1=case(x!=0,t*prevt)
| streamstats current=false last(z1) as prevz1
| eval final=(prevz1-t)/t
| eval net=round(case((row%3)=0,final),2)
| table ID,Ask,Bid,net

[dailv1808]

I tried with this search command but nothing displayed in the net field.
see my screenshot: https://imgur.com/a/ZuOl4