Splunk Search

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

somesoni2
Revered Legend

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

wpreston
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

woodcock
Esteemed Legend

You should check out the timewrap app:

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

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...