Splunk Search
Highlighted

How to calculate percent increase of crime by month over 6 years?

Engager

alt text

Hello, I'm new to Splunk and would appreciate any help.
I am trying to figure out what month had the largest percent increase in crime from 2011-2017.
I was able to list all the values out and add the totals but I'm not sure where to go from here.

Thank you for you help.

0 Karma
Highlighted

Re: How to calculate percent increase of crime by month over 6 years?

SplunkTrust
SplunkTrust

Give this a try

sourcetype=csv | stats count by date_month date_year 
| appendpipe [| stats first(count) as from last(count) as to by date_month | eval count=round(to-from)*100/from,2) | eval date_year="PercentChange"| table date_month date_year count] 
| appendpipe [| stats sum(count) as count by date_month | eval date_year="Total"| table date_month date_year count] 
| xyseries date_month date_year count

The first line get count in following format

date_month, date_year, count
april, 2011, xxx
...
april,2017,yyy
..other months say way 2011 to 2017...

The first appendpipe takes the first count (for 2011) and last count (for 2017) and calculates % change. The column names are adjusted to follow the output of first line (datemonth, dateyear as label percent and count as percent value). Output of this is added to the end of the output so far.

date_month, date_year, count
..above output..
april, percentchange,zzzz
..similarly for other months...

The second appendpipe is to calculate total for the month. The columns names are again formatted to match the output of first line (datemonth, dateyear as label Total and count as total value). Output of this is added to the end of the output so far.

The xyseries will give output similar to chart command, where all datemonth will come as row, all dateyear values as column, include PercentChange and Total, and corresponding values will be populated.

View solution in original post

0 Karma
Highlighted

Re: How to calculate percent increase of crime by month over 6 years?

Engager

Thank you! There was an error with the eval command but I was able to fix it and I also added:

| fieldformat Percent=tostring(Percent) + "%"

to format "Percent".

Highlighted

Re: How to calculate percent increase of crime by month over 6 years?

Explorer

hello,

@Kassandral, could you please tell my how do you fixe the Eval error ?

Thanks a lot,

Romain

0 Karma
Highlighted

Re: How to calculate percent increase of crime by month over 6 years?

SplunkTrust
SplunkTrust

The eval is missing an open parenthesis...

| eval count=round((to-from)*100/from,2)

0 Karma
Highlighted

Re: How to calculate percent increase of crime by month over 6 years?

Builder

hey somesoni2
in the second appendpipe, when you calculate the totals, I think you are also including the figures from the first appendpipe, and then the final figure is wrong.

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.