Archive

## help for calculating a trend in a table panel

Contributor

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

Tags (1)
Motivator

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)
``````
Contributor

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

Motivator

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

Path Finder

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

Contributor

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

Ultra Champion
``````| 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?

Contributor

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

Contributor

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

Ultra Champion
``````| 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?

Contributor

yes thanks

Ultra Champion