Splunk Search
Highlighted

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)?

Path Finder

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.

0 Karma
Highlighted

Re: 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)?

Esteemed Legend

You should check out the timewrap app:

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

0 Karma
Highlighted

Re: 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)?

Motivator

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
0 Karma
Highlighted

Re: 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)?

SplunkTrust
SplunkTrust

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
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.