help for calculating a trend in a table panel

[jip31]

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

[bowesmana]

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)

[jip31]

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

[bowesmana]

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

[jitendragupta]

try this:

index="toto" sourcetype="tutu" assigned_group="titi"
| dedup incident_number
| eval Month=strftime(_time,"%Y-%m")
| stats count by Month
| sort -Month
| delta count as Diff
| eval percentageTrend =(Diff/count)*100

[jip31]

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

[to4kawa]

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

[jip31]

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

[jip31]

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

[to4kawa]

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

[jip31]

yes thanks

[to4kawa]

please accept the answer