topic Re: help for calculating a trend in a table panel in Splunk Search

help for calculating a trend in a table panel

jip31 — Tue, 10 Dec 2019 08:48:38 GMT

hello

from the code below, i would like to be able to add a new colum in my table panel which calculate the percentage trend between the previous count and the next count for each line
Example

Month count Trend
January 200 --
February 300 +34%
March 100 -66%

index="toto" sourcetype="tutu" assigned_group="titi" 
| dedup incident_number 
| eval Month=strftime(_time,"%Y-%m") 
| stats count by Month 
| sort -Month

thanks for your help

Re: help for calculating a trend in a table panel

jitendragupta — Tue, 10 Dec 2019 09:12:46 GMT

try this:

Re: help for calculating a trend in a table panel

bowesmana — Tue, 10 Dec 2019 09:58:30 GMT

Are your example numbers what you want?

Between Jan and Feb

300 is 50% more than 200 (b > a)
200 is 33.3% less than 300 (a < b)

by between Feb and Mar

300 is 200% more than 100 (a > b)
100 is 66% less than 200 (b < a)

so in your example are you looking to show,

a) when negative, what % the smaller value is of the larger value
b) when positive, what % the difference between the values is of the larger value

or something else.

Note that the sort -Month will sort in reverse chronological order, so with the %Y-%m your order will be March,Feb,Jan. Naturally if you then use delta, that will give different results.

So you could use

| eval Month=strftime(_time,"%Y-%m") 
| stats count by Month 
| sort Month
| eval diff=0
| delta count as diff
| eval percentage=round(diff/count*100,0)

Note that if you change the sort order the delta values are different. In the ascending sort, then change from 300 to 100 shows -200%.

So it sort of depends if Feb in your example is needed to show

a) Feb is a 50% growth over Jan
b) Jan was 33% lower than Feb

but to give you your example, you would have to do

| sort Month 
| eval diff=0 
| delta count as diff
| eval percentage=round(if(diff>0,diff/count*100,diff/(count-diff)*100),0)

NB: Ascending sort

Run anywhere example

| makeresults 
| rename COMMENT as "Setting up data to show example" 
| eval f="2019-01,200#2019-02,300#2019-03,100" 
| makemv delim="#" f 
| mvexpand f 
| rex field=f "(?<Month>[^,]*),(?<count>.*)" 
| fields - f, _time 
| sort Month 
| eval diff=0 
| delta count as diff
| eval percentage=round(if(diff>0,diff/count*100,diff/(count-diff)*100),0)

Re: help for calculating a trend in a table panel

to4kawa — Tue, 10 Dec 2019 12:35:49 GMT

| makeresults count=2 
| streamstats count 
| eval _time = if (count==2,relative_time(_time,"-1y@month"), _time) 
| makecontinuous span=1d 
| eval incident_number="incedent_number_".(random() % 25 + 1)
| bin span=1month _time
| stats dc(incident_number) as count by _time
| eval Month=strftime(_time,"%B, %y") 
| delta count as diff
| eval Trend=(diff / count * 100)."%"
| table Month count Trend

Is Month in this format?

Re: help for calculating a trend in a table panel

jip31 — Tue, 10 Dec 2019 14:19:16 GMT

thanks it works but the calculation of the percentageTrend is strange
to my mind its not Diff/ count but (count-1/count)*100?

Re: help for calculating a trend in a table panel

jip31 — Tue, 10 Dec 2019 14:20:09 GMT

Thanks it seems to be good
Month format is 😐 eval Month=strftime(_time,"%Y-%m")

Re: help for calculating a trend in a table panel

jip31 — Tue, 10 Dec 2019 14:27:36 GMT

hi perfect demonstration but for the percentage calculation i have the same question asked to
jitendragupta

Re: help for calculating a trend in a table panel

jip31 — Tue, 10 Dec 2019 15:05:08 GMT

last question : is it possible to have + between the Trend when the Trend is positive??

Re: help for calculating a trend in a table panel

to4kawa — Tue, 10 Dec 2019 19:40:56 GMT

| makeresults count=2 
| streamstats count 
| eval _time = if (count==2,relative_time(_time,"-2y@month"), _time) 
| makecontinuous span=1d 
| eval incident_number="incedent_number_".(random() % 25 + 1) 
| bin span=1month _time 
| stats dc(incident_number) as count by _time 
| eval Month=strftime(_time,"%Y-%m") 
| delta count as diff 
| eval Trend=(diff / count * 100) 
| autoregress Trend as Trend_diff 
| eval positive_diff=if(Trend >= 0 , Trend - Trend_diff,NULL) 
| eval Trend=Trend."%", Trend_diff=positive_diff."%" 
| table Month count Trend Trend_diff

Is this it?

Re: help for calculating a trend in a table panel

jip31 — Fri, 13 Dec 2019 08:54:12 GMT

yes thanks

Re: help for calculating a trend in a table panel

to4kawa — Fri, 13 Dec 2019 11:42:32 GMT

please accept the answer

Re: help for calculating a trend in a table panel

bowesmana — Wed, 18 Dec 2019 08:27:12 GMT

I guess you need to express what the percentage is that you wish to see