How to calculate a rolling quarters growth rate, so if the current month is August, then growth rate = (May+June+July 2015)/(May+June+July 2014)?

[hqw]

Hi all,

I want to calculate the rolling quarters growth rate, which is calculated from the sum of the previous 3 months' data compared with the same period last year. Do you have any idea how to get this? Based on my current search, I can get the data as below

for example:
2014-04 190
2014-05 188
2014-06 188
2014-07 191
2014-08 228
2014-09 227
2014-10 224
2014-11 230
2014-12 227
2015-01 226
2015-02 225
2015-03 237
2015-04 426
2015-05 513
2015-06 463
2015-07 438
2015-08 414

This month is August, so I need the sum of May, June, and July which is (513+463+438)/(188+188+191)-1. (188+188+191 ) is the sum of last May, June, and July. If this month is September, then the sum should be changed to June, July, and August. It should always sum the previous 3 months' data and compare with the same period of last year.

Can anyone help me out?

Thanks in advance.

[somesoni2]

Try something like this.. (Run anywhere query from your sample data, replace everything before |streamstats with your base search when implementing)

| gentimes start=-1 | eval temp="2014-04 190#2014-05 188#2014-06 188#2014-07 191#2014-08 228#2014-09 227#2014-10 224#2014-11 230#2014-12 227#2015-01 226#2015-02 25#2015-03 237#2015-04 426#2015-05 513#2015-06 463#2015-07 438#2015-08 414" | table temp | makemv temp delim="#" | mvexpand temp | eval Date=mvindex(split(temp," "),0) | eval count=mvindex(split(temp," "),1) | fields - temp 
| streamstats window=4 current=f sum(count) as qAvg | eval Month=mvindex(split(Date,"-"),1) | where Month=strftime(now(),"%m")  | stats first(qAvg) as denom last(qAvg) as numerator | eval CurrentQAvg=numerator/denom

[wpreston]

I'd recommend using eval's relative_time function to set up your time bounds and then stats and eval to set up your calculations. Something like below.

Note that you will need to change the "sum_field" in both of the below eval statements to be the field you want summed.

... your base search | eval Current_Quarter = case(_time <= relative_time(now(), "@mon") AND _time >= relative_time(now(), "@mon-3mon"), FIELD_TO_BE_SUMMED,null()) | eval Quarter_Last_Year = if(_time <= relative_time(now(), "@mon-12mon") AND _time >= relative_time(now(), "@mon-15mon"), FIELD_TO_BE_SUMMED, null()) | stats sum(Current_Quarter) sum(Quarter_Last_Year) | eval PercentageGrowth=Current_Quarter / Quarter_Last_Year

[woodcock]

You should check out the timewrap app:

https://splunkbase.splunk.com/app/1645/