topic Re: How to calculate percent increase of crime by month over 6 years? in Splunk Search

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

KassandraI — Sat, 25 Feb 2017 01:16:32 GMT

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.

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

somesoni2 — Tue, 29 Sep 2020 12:59:07 GMT

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 (date_month, date_year 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 (date_month, date_year 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 date_month will come as row, all date_year values as column, include PercentChange and Total, and corresponding values will be populated.

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

KassandraI — Sat, 25 Feb 2017 03:13:56 GMT

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

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

rflouquet — Mon, 03 Apr 2017 14:44:55 GMT

hello,

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

Thanks a lot,

Romain

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

DalJeanis — Mon, 03 Apr 2017 17:03:39 GMT

The eval is missing an open parenthesis...

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

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

asimagu — Tue, 04 Apr 2017 09:28:04 GMT

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.