How do I calculate a field with another field value from a previous row?

[maria2691]

Hello Everyone

I have a below query that gives me output with 4 fields.

sourcetype=*
| fillnull TimesRan value=1 
| bucket span=1mon _time 
| stats sum(TimesRan) as timesran by _time 
| streamstats current=f window=1 last(timesran) as prev 
| eval "Change%"=if (isnull(prev),1,round((timesran-prev)*100/prev,0))

The fields returned are _time (divided by Month), timesran (Number of times Ran), prev (Number of times ran on Previous month), Change%(Percentage difference between current month compared to previous month).

Now what I need in the output is _time, Expected and timesran.

The timesran in each row should be calculated with the Change% of previous month and the Expected should be displayed in Current row.

If I use | eval Expected=timesran+(('Change%'*timesran)/100) following the above query, it is using the Change% of the same month and this will not make any sense. So I should be able to calculate the Change% of previous event/row's Change% in this particular eval. How can I do that?

Below are some sample of my current search query.

_time timesran Change% prev
2017-05 77359 1

2017-06 52663 -32 77359
2017-07 43185 -18 52663
2017-08 17743 -59 43185

Please help me on this regard!

Thanks
Maria Arokiaraj

[somesoni2 · Accepted solution]

Try this

sourcetype=*
 | fillnull TimesRan value=1 
 | bucket span=1mon _time 
 | stats sum(TimesRan) as timesran by _time 
 | streamstats current=f window=1 last(timesran) as prev 
 | eval change=if (isnull(prev),1,round((timesran-prev)*100/prev,0))
 | reverse 
 | streamstats current=f window=1 last(change) as prevchange 
 | eval Expected=timesran+((change*timesran)/100)
 | reverse | table _time Expected timesran

[maria2691]

Hello @somesoni2

I am still getting the same old results 😞

I have added change field in the result table to explain you more in detail.

_time Expected timesran change
2017-05 78132.59 77359 1
2017-06 35810.84 52663 -32
2017-07 35411.7 43185 -18
2017-08 7274.629999999999 17743 -59
2017-09 50664.509999999995 29979 69
2017-10 41117.31 35143 17
2017-11 60423.75 46125 31
2017-12 44452.8 45360 -2
2018-01 116321.6 72701 60

In the above table every line's Expected is calculated with the timesran and the same row's change.
I need it to be calculated with it's previous row's change.
For eg, for 2018-01 row the Expected is 116321.6 because the timesran is calculated with change 60 which is on the same row. It is a change from previous timesran. What I need is, the timesran to be calculated from the previous month's -2. The same way for all other rows. Is it possible in any way?

Thanks
Maria Arokiaraj

[maria2691]

@somesoni2 & everyone

Is there any way in Splunk to achieve my above requirement? Please help!

Thanks

[somesoni2]

Give this a try

Fixed syntax error

 sourcetype=*
  | fillnull TimesRan value=1 
  | bucket span=1mon _time 
  | stats sum(TimesRan) as timesran by _time 
  | streamstats current=f window=1 last(timesran) as prev 
  | eval change=if (isnull(prev),1,round((timesran-prev)*100/prev,0))
 | streamstats current=f window=1 last(change) as prevchange
  | eval Expected=timesran+(coalesce(prevchange,1)*timesran)/100
 | table _time Expected timesran

[maria2691]

Hello @somesoni2

Looks to be a small syntax issue. There was an error ) is missing, hence I added it like in below line.
| eval change=if (isnull(prev),1,round((timesran-prev)*100)/prev,0))

Now the error is The arguments to the 'if' function are invalid.
I tried to find the root cause but for me everything seems to be proper. Can you find the root cause?

Thanks

[somesoni2]

Try the fixed one.

[maria2691]

This still doesn't work as expected @somesoni2.

_time Expected timesran change
2017-05 78132.59 77359 1
2017-06 53189.63 52663 -32
2017-07 29365.8 43185 -18
2017-08 14549.26 17743 -59
2017-09 12291.39 29979 69
2017-10 59391.67 35143 17
2017-11 53966.25 46125 31
2017-12 59421.6 45360 -2
2018-01 71246.98 72701 60

The Expected is not calculated with Previous line's Timesran and Percentage. The 2017-05's timesran+(timesran*change/100) should the Expected for 2017-06. 2017-06's timesran+(timesran*change/100) should be the Expected value for 2017-07 row. Very sorry that I did not explain it clearly in my previous comments 😞 Please help.

Thanks
Maria Arokiaraj

[somesoni2]

Give this a try. Calculation for current row's expected value is done based on prev (previous timesran) and prevchange (change% of previous row)

sourcetype=*
   | fillnull TimesRan value=1 
   | bucket span=1mon _time 
   | stats sum(TimesRan) as timesran by _time 
   | streamstats current=f window=1 last(timesran) as prev 
   | eval change=if (isnull(prev),1,round((timesran-prev)*100/prev,0))
  | streamstats current=f window=1 last(change) as prevchange
   | eval Expected=prev+(coalesce(prevchange,1)*prev)/100
  | table _time Expected timesran

[maria2691]

You're Awesome @somesoni2. Thanks a lot. It worked 🙂