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.
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.
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".
@Kassandral, could you please tell my how do you fixe the Eval error ?
Thanks a lot,
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.